Month-1 and month-2 retention by sign-up cohort
Investors always ask: do new users stick around? Group users into cohorts by sign-up month (YYYY-MM of signup_date). A user is month-1 active if they played at least one track in the calendar month right after their sign-up month, and month-2 active for the month after that. Only include cohorts that signed up before 2026-05-01, so that both follow-up months fall inside the data.
Task: return cohort_month, cohort_size (all users of the cohort, active or not), m1_active, m2_active, and m1_pct (month-1 active users divided by cohort size times 100, ROUND(x, 2)). Sort by cohort_month. Months with zero active users still show 0.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| artist_id INTEGER | artist_name TEXT | home_country TEXT | genre TEXT |
|---|---|---|---|
| 1 | Tobi Adeyemi | Nigeria | Afrobeats |
| 2 | Nala Osei | Ghana | Afrobeats |
| 3 | Neon Hanbok | South Korea | K-Pop |
| 4 | Pixel Orchid | South Korea | K-Pop |
| 5 | Luz de Alba | Mexico | Latin Pop |
| 6 | Rio Caliente | Brazil | Latin Pop |
Showing the first 6 of 16 rows.
| plan_id INTEGER | plan_name TEXT | monthly_price REAL |
|---|---|---|
| 1 | Free | 0 |
| 2 | Student | 4.99 |
| 3 | Premium | 9.99 |
| 4 | Family | 14.99 |
| play_id INTEGER | user_id INTEGER | track_id INTEGER | played_at TEXT | seconds_played INTEGER | device TEXT |
|---|---|---|---|---|---|
| 1 | 2 | 15 | 2025-02-10 09:10:00 | 174 | mobile |
| 2 | 11 | 7 | 2025-03-12 11:10:00 | 253 | smart_speaker |
| 3 | 13 | 33 | 2025-03-25 14:26:23 | 140 | desktop |
| 4 | 13 | 13 | 2025-03-25 14:51:34 | 245 | smart_speaker |
| 5 | 12 | 13 | 2025-04-01 12:10:00 | 272 | desktop |
| 6 | 10 | 13 | 2025-04-11 10:10:00 | 272 | smart_tv |
Showing the first 6 of 365 rows.
| sub_id INTEGER | user_id INTEGER | plan_id INTEGER | start_date TEXT | end_date TEXT | status TEXT |
|---|---|---|---|---|---|
| 1 | 1 | 3 | 2025-01-07 | 2025-09-03 | cancelled |
| 2 | 1 | 3 | 2025-11-30 | 2026-01-02 | cancelled |
| 3 | 3 | 3 | 2025-01-20 | 2025-08-23 | cancelled |
| 4 | 5 | 2 | 2025-01-28 | NULL | active |
| 5 | 6 | 4 | 2025-02-02 | 2025-03-27 | cancelled |
| 6 | 7 | 3 | 2025-02-06 | 2025-10-04 | cancelled |
Showing the first 6 of 51 rows.
| track_id INTEGER | title TEXT | artist_id INTEGER | release_year INTEGER | duration_sec INTEGER |
|---|---|---|---|---|
| 1 | Midnight Lagos | 1 | 2019 | 353 |
| 2 | Golden Highway | 1 | 2024 | 156 |
| 3 | Paper Lanterns | 1 | 2022 | 253 |
| 4 | Neon Rain | 2 | 2025 | 249 |
| 5 | Velvet Satellite | 2 | 2026 | 176 |
| 6 | Broken Harbor | 2 | 2020 | 157 |
Showing the first 6 of 48 rows.
| user_id INTEGER | name TEXT | country TEXT | signup_date TEXT | plan_id INTEGER | birth_year INTEGER |
|---|---|---|---|---|---|
| 1 | Aarav Iyer | India | 2025-01-07 | 1 | 1998 |
| 2 | Diego Salazar | Mexico | 2025-01-12 | 1 | 1982 |
| 3 | Lucas Almeida | Brazil | 2025-01-17 | 1 | 1976 |
| 4 | Oliver Hartley | United Kingdom | 2025-01-22 | 1 | 1978 |
| 5 | Priya Banerjee | United Kingdom | 2025-01-27 | 2 | 1999 |
| 6 | Jordan Whitaker | United States | 2025-02-01 | 1 | 2006 |
Showing the first 6 of 60 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
Turn every month into a single number (year * 12 + month) so 'next month' is simply +1, even across New Year.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.