i have two tables shopper :

and store:

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

CodePudding user response：

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.