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

CodePudding user response：

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;

CodePudding user response：

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);