Monthly Signup Cohort Retention — SQL Dates Problem
The retention chart groups users into cohorts by signup month and asks what share of each cohort came back in the calendar month right after signup (month…
- Difficulty: Hard
- Topics: Dates, Aggregation, Joins
- Dialect: MySQL
- Problem: #418
Problem statement
The retention chart groups users into cohorts by signup month and asks what share of each cohort came back in the calendar month right after signup (month 1) and the month after that (month 2). Activity in the signup month itself does not count, and a user active several times in a month counts once.
Return one row per cohort with cohort_month ('YYYY-MM'), cohort_size, month_1_pct and month_2_pct — each 100 × retained users ÷ cohort_size, rounded to 1 decimal (0.0 when nobody returned). Order by cohort_month.
Tables
Table: users
| Column | Type |
|---|---|
| user_id | int |
| signed_up_on | date |
Primary key: user_id.
One row per signup.
Table: activity
| Column | Type |
|---|---|
| activity_id | int |
| user_id | int |
| active_on | date |
Primary key: activity_id.
One row per day a user was active (several rows may share a month); user_id always names a row of users.
Examples
Example 1
users
| user_id | signed_up_on |
|---|---|
| 1 | 2024-01-10 |
| 2 | 2024-01-25 |
| 3 | 2024-01-31 |
| 4 | 2024-02-05 |
| 5 | 2024-02-20 |
| 6 | 2024-03-02 |
activity
| activity_id | user_id | active_on |
|---|---|---|
| 1 | 1 | 2024-01-12 |
| 2 | 1 | 2024-02-03 |
| 3 | 1 | 2024-02-20 |
| 4 | 2 | 2024-03-15 |
| 5 | 3 | 2024-02-29 |
| 6 | 4 | 2024-03-10 |
| 7 | 5 | 2024-04-01 |
| 8 | 6 | 2024-03-05 |
Output
| cohort_month | cohort_size | month_1_pct | month_2_pct |
|---|---|---|---|
| 2024-01 | 3 | 66.7 | 33.3 |
| 2024-02 | 2 | 50 | 50 |
| 2024-03 | 1 | 0 | 0 |
How to solve Monthly Signup Cohort Retention
Retention is about calendar months relative to signup, so the cleanest representation is a month number: YEAR * 12 + MONTH. Month 1 for a cohort is then cohort key + 1, month 2 is + 2, and December rolls into January without special cases. Days do not matter — user 3 signed up on 31 January and was active on 29 February, which is month 1, even though only a few weeks passed.
Activity is reduced to distinct (user, month) pairs and LEFT JOINed to the cohorts, so a user who never returned still counts in cohort_size. Within each cohort, COUNT(DISTINCT CASE WHEN a.month_key = c.month_key + 1 THEN c.user_id END) counts the month-1 returners and the same with + 2 counts month 2; activity in the signup month matches neither. The shares are rounded to one decimal, which also keeps MySQL's four-decimal division out of the answer.
The alternative builds a 0/1 flag per user with EXISTS, comparing the activity's 'YYYY-MM' with that of DATE_ADD(signed_up_on, INTERVAL n MONTH) — adding months to 31 January lands on 29 February, still the right month. Averaging flags per cohort is the same ratio. The join version is one pass over each table; the EXISTS version is a lookup per user per month.
Reference solution (MySQL)
WITH cohorts AS (
SELECT user_id, DATE_FORMAT(signed_up_on, '%Y-%m') AS cohort_month,
YEAR(signed_up_on) * 12 + MONTH(signed_up_on) AS month_key
FROM users
), active_months AS (
SELECT DISTINCT user_id, YEAR(active_on) * 12 + MONTH(active_on) AS month_key
FROM activity
)
SELECT c.cohort_month,
COUNT(DISTINCT c.user_id) AS cohort_size,
ROUND(100 * COUNT(DISTINCT CASE WHEN a.month_key = c.month_key + 1 THEN c.user_id END) / COUNT(DISTINCT c.user_id), 1) AS month_1_pct,
ROUND(100 * COUNT(DISTINCT CASE WHEN a.month_key = c.month_key + 2 THEN c.user_id END) / COUNT(DISTINCT c.user_id), 1) AS month_2_pct
FROM cohorts c
LEFT JOIN active_months a ON a.user_id = c.user_id
GROUP BY c.cohort_month
ORDER BY c.cohort_monthAnother way
SELECT cohort_month, COUNT(*) AS cohort_size,
ROUND(100 * SUM(m1) / COUNT(*), 1) AS month_1_pct,
ROUND(100 * SUM(m2) / COUNT(*), 1) AS month_2_pct
FROM (
SELECT DATE_FORMAT(u.signed_up_on, '%Y-%m') AS cohort_month,
CASE WHEN EXISTS (SELECT 1 FROM activity a WHERE a.user_id = u.user_id
AND DATE_FORMAT(a.active_on, '%Y-%m') = DATE_FORMAT(DATE_ADD(u.signed_up_on, INTERVAL 1 MONTH), '%Y-%m')) THEN 1 ELSE 0 END AS m1,
CASE WHEN EXISTS (SELECT 1 FROM activity a WHERE a.user_id = u.user_id
AND DATE_FORMAT(a.active_on, '%Y-%m') = DATE_FORMAT(DATE_ADD(u.signed_up_on, INTERVAL 2 MONTH), '%Y-%m')) THEN 1 ELSE 0 END AS m2
FROM users u
) flags
GROUP BY cohort_month
ORDER BY cohort_month← Onboarding Funnel With Steps Taken in Order · Longest Daily Usage Streak per User →