Day-1 and Day-7 Retention by Signup Week

Hard
192.

Day-1 and Day-7 Retention by Signup Week

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

ColumnType
player_idint
signup_datedate
acquisition_channelvarchar

Primary key: player_id.

One row per new account.

Table: PlayDay

ColumnType
player_idint
play_datedate
minutes_playedint

Primary key (together): player_id, play_date.

A daily roll-up: one row per player per day they played, never before their signup date.

Example

The rows must come back in the order shown.

Player

player_idsignup_dateacquisition_channel
12025-03-03organic
22025-03-05youtube_ad
32025-03-09referral
42025-03-10organic
52025-03-11youtube_ad

PlayDay

player_idplay_dateminutes_played
12025-03-0340
12025-03-0425
12025-03-1030
22025-03-0515
22025-03-1260
32025-03-1045
42025-03-1020
52025-03-1235
52025-03-1810

Output

cohort_weekcohort_sized1_retention_pctd7_retention_pct
2025-03-03366.6766.67
2025-03-1025050
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
player_idint
signup_datedate
acquisition_channelvarchar

Primary key: player_id.

One row per new account.

Table: PlayDay

ColumnType
player_idint
play_datedate
minutes_playedint

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_idsignup_dateacquisition_channel
12025-03-03organic
22025-03-05youtube_ad
32025-03-09referral
42025-03-10organic
52025-03-11youtube_ad

PlayDay

player_idplay_dateminutes_played
12025-03-0340
12025-03-0425
12025-03-1030
22025-03-0515
22025-03-1260
32025-03-1045
42025-03-1020
52025-03-1235
52025-03-1810

Output

cohort_weekcohort_sized1_retention_pctd7_retention_pct
2025-03-03366.6766.67
2025-03-1025050

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_week

Another 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 →