CODEWITHSUNDEEP
mysqlsqlteradata
Ninjia123
Ninjia123

Reputation: 47

How to find out customers that only shopped in one store using SQL

I want to see the number of customers who only shopped in one specific store during a specific period, so I wrote the following SQL:

SELECT COUNT (DISTINCT CARD_NUMBER)
FROM <TRANSACTION TABLE>
WHERE DATE BETWEEN ‘XXXX-XX-XX’(DATE) AND ‘XXXX-XX-XX’(DATE)
AND STORE_NUMBER = 1234;

What if these customers (card holders) shop in other store chains?

I couldn’t use AND CARD_NUMBER NOT IN… Because we have too many stores. Any syntax that could be used here to solve this problem?

Upvotes: 0

Views: 1118

Answers (6)

Wuxing Li
Wuxing Li

Reputation: 1

With card_list as (
-- get the custs that shopped 1234
select CARD_NUMBER
FROM <TRANSACTION TABLE>
WHERE DATE BETWEEN ‘XXXX-XX-XX’(DATE) AND ‘XXXX-XX-XX’(DATE)
AND STORE_NUMBER = 1234

EXCEPT
-- Remove from the above list those that shopped other stores

-- get the custs that shopped all other stores
select CARD_NUMBER
FROM <TRANSACTION TABLE>
WHERE DATE BETWEEN ‘XXXX-XX-XX’(DATE) AND ‘XXXX-XX-XX’(DATE)
AND STORE_NUMBER <> 1234
)

-- get the counts, this is already a unique list so no distinct needed.
select count(CARD_NUMBER) from card_list;'

Upvotes: 0

Dean Clark
Dean Clark

Reputation: 3868

As I mentioned in my comment, many database optimizers will rewrite a NOT IN (...) to a NOT EXISTS. In your case either a NOT EXISTS and a LEFT OUTER JOIN would work...

Using NOT EXISTS:

SELECT COUNT (DISTINCT CARD_NUMBER)
FROM <TRANSACTION TABLE> T
WHERE DATE BETWEEN ‘XXXX-XX-XX’(DATE) AND ‘XXXX-XX-XX’(DATE)
AND NOT EXISTS (
  SELECT 1
  FROM <TRANSACTION TABLE> T
  WHERE DATE BETWEEN ‘XXXX-XX-XX’(DATE) AND ‘XXXX-XX-XX’(DATE)
  AND STORE_NUMBER <> 1234
)
AND STORE_NUMBER = 1234;

Using LEFT OUTER JOIN:

SELECT COUNT (DISTINCT CARD_NUMBER)
FROM <TRANSACTION TABLE> T
LEFT OUTER JOIN  <TRANSACTION TABLE> CHECK
    ON  T.CARD_NUMBER = CHECK.CARD_NUMBER
    AND CHECK.DATE BETWEEN ‘XXXX-XX-XX’(DATE) AND ‘XXXX-XX-XX’(DATE)
    AND STORE_NUMBER <> 1234
WHERE T.DATE BETWEEN ‘XXXX-XX-XX’(DATE) AND ‘XXXX-XX-XX’(DATE)
AND T.STORE_NUMBER = 1234
AND CHECK.CARD_NUMBER IS NULL;

Upvotes: 0

Kyle
Kyle

Reputation: 115

SELECT COUNT(*)
FROM
 ( 
   SELECT CARD_NUMBER
    FROM <TRANSACTION TABLE>
    WHERE DATE BETWEEN ‘XXXX-XX-XX’(DATE) AND ‘XXXX-XX-XX’(DATE)
    AND STORE_NUMBER = '1234'
    GROUP BY CARD_NUMBER
    HAVING COUNT(CARD_NUMBER) = 1
  ) dt;

Upvotes: -1

StoneGiant
StoneGiant

Reputation: 1497

If I understand your question, you want to know how many customers shopped ONLY at store 1234 during a given time period.

I think this does it:

SELECT COUNT (DISTINCT CARD_NUMBER)
  FROM <TABLE>
 WHERE DATE BETWEEN ‘XXXX-XX-XX’(DATE) AND ‘XXXX-XX-XX’(DATE)
   AND STORE_NUMBER = 1234
   AND CARD_NUMBER NOT IN (SELECT DISTINCT CARD_NUMBER
                             FROM <TABLE>
                            WHERE DATE BETWEEN ‘XXXX-XX-XX’(DATE)
                                           AND ‘XXXX-XX-XX’(DATE)
                              AND STORE_NUMBER <> 1234);

The sub-select is giving you a list of card numbers where that shopped somewhere else during that time period. So, you're saying, "Show me all the card numbers from a given date for store 1234 where that number doesn't appear in the list of card numbers used at a different store."

Hope that helps

Upvotes: 1

dnoeth
dnoeth

Reputation: 60482

If all transactions are within a single store, then MIN & MAX are the same:

SELECT COUNT(*)
FROM
 ( 
   SELECT CARD_NUMBER
   FROM <TRANSACTION TABLE>
   WHERE DATE BETWEEN ‘XXXX-XX-XX’(DATE) AND ‘XXXX-XX-XX’(DATE)
   GROUP BY CARD_NUMBER
   HAVING MIN(STORE_NUMBER) = 1234  -- shopped in this store
       AND MAX(STORE_NUMBER) = 1234  -- but no other store 
 ) dt;

Upvotes: 2

Micho Rizo
Micho Rizo

Reputation: 1092

add another select statement in your NOT IN clause ... AND NOT IN (SELECT DISTINCT STORE_NUMBER FROM FOO WHERE STORE_NUMBER != 1234)

Upvotes: 0

Related Questions