First-Week Retention by Sign-Up Month — SQL Dates Problem
Product reviews first-week retention by sign-up cohort. A user is retained when they opened the app on any day from 1 to 7 days after their sign-up date…
- Difficulty: Hard
- Topics: Dates, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #444
Problem statement
Product reviews first-week retention by sign-up cohort. A user is retained when they opened the app on any day from 1 to 7 days after their sign-up date (opening the app on the sign-up day itself does not count). A cohort is all users who signed up in the same calendar month.
Return the columns cohort_month ('YYYY-MM'), cohort_size, retained and retention_pct (retained × 100 ÷ cohort size, rounded to 1 decimal) for every month that has sign-ups, ordered by cohort_month. A user's several opens count once.
Tables
Table: users
| Column | Type |
|---|---|
| user_id | int |
| signed_up_on | date |
| acquisition_channel | varchar |
Primary key: user_id.
One row per account.
Table: app_opens
| Column | Type |
|---|---|
| user_id | int |
| opened_on | date |
Primary key: user_id, opened_on.
One row per user per day they opened the app; user_id is always a row of users.
Examples
Example 1
users
| user_id | signed_up_on | acquisition_channel |
|---|---|---|
| 1 | 2025-01-05 | instagram_ads |
| 2 | 2025-01-20 | referral |
| 3 | 2025-01-31 | organic |
| 4 | 2025-02-02 | referral |
| 5 | 2025-02-14 | organic |
| 6 | 2025-02-27 | instagram_ads |
app_opens
| user_id | opened_on |
|---|---|
| 1 | 2025-01-05 |
| 1 | 2025-01-12 |
| 2 | 2025-01-20 |
| 2 | 2025-01-28 |
| 3 | 2025-02-01 |
| 3 | 2025-02-03 |
| 4 | 2025-02-09 |
| 6 | 2025-03-06 |
Output
| cohort_month | cohort_size | retained | retention_pct |
|---|---|---|---|
| 2025-01 | 3 | 2 | 66.7 |
| 2025-02 | 3 | 2 | 66.7 |
How to solve First-Week Retention by Sign-Up Month
Retention questions go in two levels. Per user, compute a 0/1 flag: did any open fall between signed_up_on + 1 and signed_up_on + 7? The solution LEFT JOINs the opens with that window in the ON clause — moving it to WHERE would drop users with no qualifying open and shrink the cohort — and turns COUNT(o.opened_on) > 0 into 1 or 0. The alternative uses EXISTS with DATEDIFF(...) BETWEEN 1 AND 7, which also counts several opens once.
Per cohort, group the users by DATE_FORMAT(signed_up_on, '%Y-%m'): COUNT(*) is the cohort size, SUM(flag) the retained users, and their ratio × 100 rounded to one decimal the percentage (the average of a 0/1 flag is the same ratio). Opens on the sign-up day, the day before or eight days after are outside the window, and a user who signed up on 31 January is retained by an open on 1 February — the window is about days since sign-up, not the calendar month.
The cost is one join of users to their opens with a range condition; the composite key (user_id, opened_on) makes it an index range scan.
Reference solution (MySQL)
SELECT DATE_FORMAT(signed_up_on, '%Y-%m') AS cohort_month,
COUNT(*) AS cohort_size,
SUM(is_retained) AS retained,
ROUND(SUM(is_retained) * 100 / COUNT(*), 1) AS retention_pct
FROM (
SELECT u.user_id, u.signed_up_on,
CASE WHEN COUNT(o.opened_on) > 0 THEN 1 ELSE 0 END AS is_retained
FROM users u
LEFT JOIN app_opens o ON o.user_id = u.user_id
AND o.opened_on BETWEEN DATE_ADD(u.signed_up_on, INTERVAL 1 DAY) AND DATE_ADD(u.signed_up_on, INTERVAL 7 DAY)
GROUP BY u.user_id, u.signed_up_on
) t
GROUP BY DATE_FORMAT(signed_up_on, '%Y-%m')
ORDER BY cohort_monthAnother way
SELECT cohort_month, COUNT(*) AS cohort_size, SUM(flag) AS retained, ROUND(AVG(flag) * 100, 1) AS retention_pct
FROM (
SELECT LEFT(u.signed_up_on, 7) AS cohort_month,
IF(EXISTS (SELECT 1 FROM app_opens o WHERE o.user_id = u.user_id
AND DATEDIFF(o.opened_on, u.signed_up_on) BETWEEN 1 AND 7), 1, 0) AS flag
FROM users u
) t
GROUP BY cohort_month
ORDER BY cohort_month← Longest Daily Posting Streak per Creator · Reshare Cascades: Size, Depth and Unique Resharers →