Loadingone moment…
Sign in to run and submit your work
Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.
or
CODE WORKSPACE
The table user_events has one row every time a user was active in a given week. A user's cohort is the week they signed up. A user counts as retained in week W if they were active in week W at all.
Write a query that answers: for each signup cohort, how many of its users were still active in each following week?
Return exactly three columns, in this order:
cohort_week - the week the user signed up
active_week - a week those users were active in
retained_users - how many distinct users from that cohort were active in that week
Count every cohort/active-week pair that has at least one user. Order the result by cohort_week, then active_week.
Result columns · in this order
cohort_weekactive_weekretained_usersHow to approach it
Work in three steps: (1) one row per user with their earliest signup_week, (2) one row per user and distinct active_week, (3) join the two and count distinct users per cohort_week and active_week.
Sample input
| user_id | signup_week | active_week |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 1 | 3 |
| 2 | 1 | 1 |
| 2 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 1 | 1 |
| 4 | 2 | 2 |
| 4 | 2 | 3 |
| 4 | 2 | 4 |
| 5 | 2 | 2 |
| 5 | 2 | 2 |
| 5 | 2 | 4 |
| 6 | 2 | 2 |
| 7 | 3 | 3 |
| 7 | 3 | 4 |
| 7 | 3 | 5 |
| 8 | 3 | 3 |
| 8 | 3 | 3 |
| 9 | 3 | 3 |
| 9 | 3 | 5 |
21 rows — scroll inside the table to see them all.
Expected output
| cohort_week | active_week | retained_users |
|---|---|---|
| 1 | 1 | 3 |
| 1 | 2 | 1 |
| 1 | 3 | 2 |
| 2 | 2 | 3 |
| 2 | 3 | 1 |
| 2 | 4 | 2 |
| 3 | 3 | 3 |
| 3 | 4 | 1 |
| 3 | 5 | 2 |
9 rows — all rows shown.
Constraints
COUNT(*) will over-count.signup_week as their cohort, so a user always lands in one cohort.Worked example
Take user 2 in the sample data. They signed up in week 1 and have three rows: week 1 twice and week 3 once. They were active in 2 distinct weeks, not 3. So user 2 adds 1 to cohort 1 / week 1 and 1 to cohort 1 / week 3, and adds nothing to cohort 1 / week 2 - the week they skipped. If your week-1 number for cohort 1 comes out as 4 instead of 3, the duplicate row is the reason.
What this tests
Picking the right grain before you aggregate, removing duplicates without dropping real activity, assigning a stable cohort per user, and explaining why each intermediate step exists.
Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.
This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.