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

Question

I have two tables:

• "purchases" table has 3 columns: "user_id", "store_id", "purchase_date" (so I can know that client_id=XXX made a purchase in store_id=YYY on date=YYYY-MM-DD).

• "stores" table has "store_id", "latitude", "longitude", "date" (so at that date=YYYY-MM-DD, the store_id=YYY was located in latitude=AAA and longitude=BBB).

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...

Answer 1

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;