7-Day Active Users in Python

An interviewer asks: "For each day, how many different users were active in the 7 days ending that day?" This is a rolling 7-day active users count. Every day gets its own window: the day itself and the 6 days before it. Product teams read this number on their dashboards every morning. If it is wrong, the business thinks it has more users than it really has.

The trap: adding up each day's active users. Someone who comes back on four days is counted four times. From 2026-05-01 to 2026-05-07 the daily counts add up to 12, but only 5 different people visited. A window function doesn't help either. A rolling sum works with a window frame, because revenue adds up day by day:

SELECT day,
    SUM(revenue) OVER (ORDER BY day
        RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW) AS revenue_7d
FROM daily_revenue

Users don't add up that way. Postgres, Spark and most warehouses also refuse COUNT(DISTINCT …) with a window frame.

The answer that works everywhere is a self-join. The table is joined to a second copy of itself under another name, so each day d picks up the rows a of the days in its window. Then you GROUP BY d.day and count with COUNT(DISTINCT a.user_id), which counts each user once, however many of their rows the day picked up. In DuckDB a date minus a number is that many days earlier: DATE '2026-05-08' - 7 is 2026-05-01. The join pairs each day with up to 7 days of rows, so build it on one row per user per day, never on raw clicks.

The hidden setup writes visits.csv: 17 visits by 6 users from 2026-05-01 to 2026-05-10. The starting code loads it into activity, with one row per day and user (two visits on the same day become one row), and starts query. The check prints your query's rows. Then it prints the trap's number: the activity rows of 05-01 to 05-07, added up.

day | active_7d
2026-05-01 | 2
2026-05-02 | 3
2026-05-03 | 3
2026-05-04 | 4
2026-05-05 | 5
2026-05-06 | 5
2026-05-07 | 5
2026-05-08 | 6
2026-05-09 | 5
2026-05-10 | 5
(10 rows)
sum_of_daily
12
(1 row)

Your task: finish query. Join activity d to a second copy, activity a, on the days up to d.day. Count the different a.user_id as active_7d. Then keep only the days after d.day - 7.

Assignment
Follow the Coding Tutorial to count each day's 7-day active users with a self-join on activity.

Hint
The window is two conditions in ON. a.day <= d.day ends it on the day, and a.day > d.day - 7 starts it 6 days earlier. Use >, not >=: with >= the window holds 8 days.