Rolling Windows

Question 1

what does PARTITION BY do in window functions?

Answer 1

You are telling SQL:

if you do partition by date:

–> Reset the window every date.
–> never do this with 7-day /30 -day windows, etc.

if you do partition by user_id
–> Reset the window every user_id.

Question 2

7 day rolling widnow

Answer 2

SELECT
event_date,
COUNT(DISTINCT user_id) OVER (
ORDER BY event_date
RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
) AS rolling_7d_active_users
FROM events;