Rolling 7 day active users

Hard

Question

The activity table has one row each time a user did something in the app, with the day active_on. A user can have many rows on one day, and some days have no rows at all.

The rolling 7 day active users of a day are the different users with at least one row in the 7 days that end on that day: the day itself and the 6 days before it.

For every day that has at least one row in the table, return day and active_users (the number of different users in the 7 days that end on that day). Sort by day.

The order of the rows matters for this question.

count distinct
correlated subquery
self join
rolling window
date
between
window frame

Read first: Lesson 49, SQL patterns for time: streaks, sessions, cohorts, funnels and rolling windows

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

activity3 columns · 16 rowsnot run yet

CREATE TABLE activity (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, active_on TEXT NOT NULL);

Example rows of the activity table
id	user_id	active_on
1	1	2026-04-01
2	2	2026-04-01
3	1	2026-04-02
4	3	2026-04-02
5	1	2026-04-02
6	2	2026-04-04
7	4	2026-04-05
8	1	2026-04-05
9	5	2026-04-09
10	2	2026-04-09
11	6	2026-04-10
12	3	2026-04-14
13	7	2026-04-14
14	5	2026-04-15
15	8	2026-04-20
16	1	2026-04-20

About this SQL

SQLite does not allow COUNT(DISTINCT x) as a window function (OVER), and a ROWS frame counts rows, not days, so it is wrong when days have no rows. Use a correlated subquery or a join on a date range instead. date(x, '-6 days') moves a date back. YYYY-MM-DD text compares in date order, so BETWEEN works on it, and BETWEEN includes both ends.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready