Next-Month Retention by Signup Cohort
Group users into cohorts by signup month (YYYY-MM). A user is retained if they have at least one activity row in the calendar month right after their signup month. For a January signup that is February, and December rolls over to January of the next year.
- cohort_size: users who signed up that month
- retained_next_month: of those, how many were retained
- retention_pct: 100.0 * retained_next_month / cohort_size, ROUND(x, 2)
Return columns cohort_month, cohort_size, retained_next_month, retention_pct. Order by cohort_month ascending.
Schema
CREATE TABLE users ( user_id INTEGER PRIMARY KEY, signup_date TEXT NOT NULL ); CREATE TABLE activity ( user_id INTEGER NOT NULL, activity_date TEXT NOT NULL );
Sample data
users
| user_id | signup_date |
|---|---|
| 1 | 2026-01-05 |
| 2 | 2026-01-20 |
| 3 | 2026-01-31 |
| 4 | 2026-02-02 |
| 5 | 2026-02-14 |
activity
| user_id | activity_date |
|---|---|
| 1 | 2026-01-06 |
| 1 | 2026-02-10 |
| 1 | 2026-02-11 |
| 2 | 2026-03-01 |
| 3 | 2026-02-01 |
| 4 | 2026-03-15 |
| 5 | 2026-02-20 |
Hint 1
date(d, 'start of month', '+1 month') gives the first day of the following month, and it handles December and the 31st safely.
Hint 2
Flag each user 0/1 with EXISTS first, then aggregate per cohort.
Hint 3
Activity a year earlier in the same month name does not count. Compare full 'YYYY-MM' strings.
Good practice for: Data Analyst, Product Manager, Data Scientist, Digital Marketing Specialist
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.