CODEWITHSUNDEEP
mysqlsqlwhere-clause
ElTitoFranki
ElTitoFranki

Reputation: 397

Count number of users that purchased in certain stores at certain dates

I have two tables:

I would like to count the number of clients that purchased in stores located at certain geographical regions at some date, and another region in another date.

For example, I want to count the number of users that purchased in stores in USA in May 2022 and also purchased in stores in Japan during June 2022.

I tried this:

SELECT count(distinct user)
FROM (
"purchases" WHERE store_id IN ( 
    SELECT store_id FROM "stores" WHERE ( latitude BETWEEN 23 AND 50 ) AND ( longitude BETWEEN -127 AND -66 ) AND ( date BETWEEN '2022-05-01' AND '2022-05-31' 
) 
AND store_id IN ( 
    SELECT store_id FROM "stores" WHERE ( latitude BETWEEN 30 AND 45 ) AND ( longitude BETWEEN 130 AND 150 ) AND ( date BETWEEN '2022-06-01' AND '2022-06-30' 
)
)

But does not work, I don't know how to do it...

Upvotes: 0

Views: 51

Answers (2)

forpas
forpas

Reputation: 164204

Use 2 levels of aggregation:

SELECT COUNT(*) count
FROM (
  SELECT p.user_id
  FROM purchases p INNER JOIN stores s
  ON s.store_id = p.store_id
  GROUP BY p.user_id
  HAVING SUM(s.latitude BETWEEN 23 AND 50 AND s.longitude BETWEEN -127 AND -66 AND p.purchase_date BETWEEN '2022-05-01' AND '2022-05-31') > 0
     AND SUM(s.latitude BETWEEN 30 AND 45 AND s.longitude BETWEEN 130 AND 150 AND p.purchase_date BETWEEN '2022-06-01' AND '2022-06-30') > 0
) t;

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Step by step with the help of CTEs (WITH clauses):

with us_stores as
(
  SELECT *
  FROM stores
  WHERE latitude BETWEEN 23 AND 50 
  AND longitude BETWEEN -127 AND -66 
)
, jp_stores as
(
  SELECT *
  FROM stores
  WHERE latitude BETWEEN 30 AND 45
  AND longitude BETWEEN 130 AND 150
)
, us_purchasers as
(
  select distinct user_id
  from purchases
  where store_id in (select store_id from us_stores)
  and date >= date '2022-05-01'
  and date < date '2022-06-01'
)
, jp_purchasers as
(
  select distinct user_id
  from purchases
  where store_id in (select store_id from jp_stores)
  and date >= date '2022-06-01'
  and date < date '2022-07-01'
)
select count(*)
from us_purchasers
join jp_purchasers using (user_id);

Upvotes: 1

Related Questions