Day-1 and Day-7 Retention by Signup Week — SQL Dates Problem
Product reviews new-player retention by signup week, a week starting on Monday: a player who signed up on Sunday 9 March 2025 belongs to the week of Monday…
- Difficulty: Hard
- Topics: Dates, Aggregation, Joins
- Dialect: MySQL
- Problem: #192
Problem statement
Product reviews new-player retention by signup week, a week starting on Monday: a player who signed up on Sunday 9 March 2025 belongs to the week of Monday 3 March. A player is day-1 retained if they played on the day right after their signup date, and day-7 retained if they played exactly 7 days after it. Playing on the signup day itself counts for neither; players who never came back still belong to their cohort.
Return cohort_week (the Monday, YYYY-MM-DD), cohort_size, d1_retention_pct and d7_retention_pct — the percentage of the cohort retained, each rounded to 2 decimal places — ordered by cohort_week.
Tables
Table: Player
| Column | Type |
|---|---|
| player_id | int |
| signup_date | date |
| acquisition_channel | varchar |
Primary key: player_id.
One row per new account.
Table: PlayDay
| Column | Type |
|---|---|
| player_id | int |
| play_date | date |
| minutes_played | int |
Primary key: player_id, play_date.
A daily roll-up: one row per player per day they played, never before their signup date.
Examples
Example 1
Player
| player_id | signup_date | acquisition_channel |
|---|---|---|
| 1 | 2025-03-03 | organic |
| 2 | 2025-03-05 | youtube_ad |
| 3 | 2025-03-09 | referral |
| 4 | 2025-03-10 | organic |
| 5 | 2025-03-11 | youtube_ad |
PlayDay
| player_id | play_date | minutes_played |
|---|---|---|
| 1 | 2025-03-03 | 40 |
| 1 | 2025-03-04 | 25 |
| 1 | 2025-03-10 | 30 |
| 2 | 2025-03-05 | 15 |
| 2 | 2025-03-12 | 60 |
| 3 | 2025-03-10 | 45 |
| 4 | 2025-03-10 | 20 |
| 5 | 2025-03-12 | 35 |
| 5 | 2025-03-18 | 10 |
Output
| cohort_week | cohort_size | d1_retention_pct | d7_retention_pct |
|---|---|---|---|
| 2025-03-03 | 3 | 66.67 | 66.67 |
| 2025-03-10 | 2 | 50 | 50 |
How to solve Day-1 and Day-7 Retention by Signup Week
Two levels again. Per player: LEFT JOIN the daily play rows and collapse them with MAX(CASE WHEN play_date = DATE_ADD(signup_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) — a 0/1 flag that is 1 if any row is the day after signup — and the same for 7 days. The LEFT JOIN matters: player 4 never played after the signup day and player 3's only row is day 1, and both must still count in their cohort. A play on the signup day matches neither CASE.
Per cohort: the week's Monday is DATE_SUB(signup_date, INTERVAL WEEKDAY(signup_date) DAY) — WEEKDAY counts days since Monday, so Sunday 9 March steps back six days to 3 March. Grouping the per-player flags by that Monday, COUNT(*) is the cohort size and 100 * SUM(flag) / COUNT(*), rounded to two places, the retention. Because a flag is 0 or 1, AVG(flag) * 100 is the same number.
The alternative computes each flag with a correlated EXISTS on DATEDIFF(play_date, signup_date) = 1 (or 7). Both read each player's play days once; an index on (player_id, play_date) makes the EXISTS lookups point queries.
Reference solution (MySQL)
WITH flags AS (
SELECT p.player_id,
DATE_SUB(p.signup_date, INTERVAL WEEKDAY(p.signup_date) DAY) AS cohort_week,
MAX(CASE WHEN d.play_date = DATE_ADD(p.signup_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) AS d1,
MAX(CASE WHEN d.play_date = DATE_ADD(p.signup_date, INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS d7
FROM Player p
LEFT JOIN PlayDay d ON d.player_id = p.player_id
GROUP BY p.player_id, p.signup_date
)
SELECT cohort_week,
COUNT(*) AS cohort_size,
ROUND(100 * SUM(d1) / COUNT(*), 2) AS d1_retention_pct,
ROUND(100 * SUM(d7) / COUNT(*), 2) AS d7_retention_pct
FROM flags
GROUP BY cohort_week
ORDER BY cohort_weekAnother way
SELECT cohort_week, COUNT(*) AS cohort_size,
ROUND(AVG(d1) * 100, 2) AS d1_retention_pct, ROUND(AVG(d7) * 100, 2) AS d7_retention_pct
FROM (
SELECT SUBDATE(p.signup_date, WEEKDAY(p.signup_date)) AS cohort_week,
CASE WHEN EXISTS (SELECT 1 FROM PlayDay d WHERE d.player_id = p.player_id AND DATEDIFF(d.play_date, p.signup_date) = 1) THEN 1 ELSE 0 END AS d1,
CASE WHEN EXISTS (SELECT 1 FROM PlayDay d WHERE d.player_id = p.player_id AND DATEDIFF(d.play_date, p.signup_date) = 7) THEN 1 ELSE 0 END AS d7
FROM Player p
) t
GROUP BY cohort_week
ORDER BY cohort_week← Longest Daily Login Streak per Player · Battle Pass Race to Tier 30, Season by Season →