Search code examples
sqlpostgresqldatetimeaggregate-functionspartitioning

compare unique buyers for each store in its first 30 days of opening? SQL

i have two tables shopper :

shopper_id date store_id
1234 2021-12-02 4321

and store:

store_id opening_date
4321 2021-11-23

i want to get the total number of unique shoppers who visited each store within the first 30 days of opening.

so far have

SELECT
    store_id,
    opening_date,
    COUNT( shopper_id ) OVER ( PARTITION BY store_id ORDER BY opening_date )

FROM
    store AS s
    INNER JOIN shoppers AS sp ON s.store_id = sp.store_id

WHERE
    opening_date <= opening_date + 30
Solution

  • This should work in your case:

    SELECT store.store_id,
  COUNT(DISTINCT(shopper_id)) as visitor_count
FROM store LEFT JOIN shopper ON store.store_id = shopper.store_id
  AND store.opening_date <= shopper.date
  AND shopper.date <= store.opening_date + interval '30 days'
GROUP BY store.store_id;

    See this fiddle for better understanding.