Users active three weeks in a row

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.

A week starts on Monday and ends on Sunday. A user is active in a week when they have at least one row on a day of that week. A run is a group of calendar weeks in a row, where the user is active in each week. Weeks can be in different years: the week of Monday 2025-12-29 is followed by the week of Monday 2026-01-05.

Find the users who have a run of at least 3 weeks. Return user_id and longest_run (the number of weeks in their longest run). The order of the rows does not matter.

gaps and islands
window function
row number
date
strftime
week
distinct

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 · 25 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	2025-12-17
2	1	2025-12-21
3	1	2025-12-23
4	1	2025-12-31
5	1	2026-01-04
6	1	2026-01-06
7	2	2026-01-06
8	2	2026-01-11
9	2	2026-01-21
10	3	2026-02-03
11	3	2026-02-03
12	3	2026-02-10
13	3	2026-02-12
14	3	2026-02-18
15	3	2026-03-04
16	4	2026-03-03
17	4	2026-03-10
18	4	2026-03-24
19	4	2026-03-31
20	5	2026-03-02
21	5	2026-03-09
22	5	2026-03-17
23	5	2026-03-26
24	5	2026-03-30
25	5	2026-04-05

About this SQL

SQLite has no DATE_TRUNC('week'). strftime('%w', x) gives the weekday as a number with Sunday = 0, Monday = 1 and Saturday = 6, so (weekday + 6) % 7 is the number of days since Monday. date(x, '-3 days') moves a date back. Do not use week numbers (%W) for runs: they start again at 0 or 1 every year. julianday(x) turns a date into a number of days, and two Mondays that are one week apart differ by 7.

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