Share of Quiz Players Who Returned the Next Day — SQL Medium Problem

Problem statement

A daily-quiz app logs one row for each day a player plays. A player's first day is the earliest played_on date they have.

Return a single row with one column, next_day_rate: the number of players who also played on the day right after their first day, divided by the number of all players, rounded to 2 decimal places. Only that one day matters — a player who next appears two days later, or who plays two days in a row only later on, does not count.

Tables

Table: QuizPlay

Primary key: player_id, played_on.

One row per player per day played. The table is never empty.

Examples

Example 1

QuizPlay

Output

How to solve Share of Quiz Players Who Returned the Next Day

The question has a fixed reference point per player — their first day — so the first step is to compute it: GROUP BY player_id with MIN(played_on) gives one row per player.

Next, for each of those rows, check whether the same player has a row dated one day later. A LEFT JOIN back to QuizPlay on the player and on played_on = DATE_ADD(first_day, INTERVAL 1 DAY) finds that row when it exists and leaves NULLs when it does not. Because a player has at most one row per day, each player still produces exactly one row, so COUNT(*) is the number of players and COUNT(q.player_id) — which skips the NULLs — is the number who returned. Their quotient, rounded, is the answer.

Use real date arithmetic. '2024-02-28' + 1 is not a date, and day 29 → day 30 does not exist in February; DATE_ADD and DATEDIFF get month ends and 29 February right. Also check the precise condition: playing on two consecutive days somewhere is a different, larger number.

The alternatives turn the check around — keep rows whose previous day is the player's first day, using a row-value IN — or compute the first day with a window MIN(…) OVER (PARTITION BY player_id) and count rows exactly one day after it. Each reads the table two or three times.

Reference solution (MySQL)

SELECT ROUND(COUNT(q.player_id) / COUNT(*), 2) AS next_day_rate
FROM (SELECT player_id, MIN(played_on) AS first_day FROM QuizPlay GROUP BY player_id) f
LEFT JOIN QuizPlay q
  ON q.player_id = f.player_id
 AND q.played_on = DATE_ADD(f.first_day, INTERVAL 1 DAY)

Another way

SELECT ROUND(
  (SELECT COUNT(*) FROM QuizPlay
   WHERE (player_id, DATE_SUB(played_on, INTERVAL 1 DAY)) IN (SELECT player_id, MIN(played_on) FROM QuizPlay GROUP BY player_id))
  / (SELECT COUNT(DISTINCT player_id) FROM QuizPlay), 2) AS next_day_rate

Another way

WITH firsts AS (SELECT player_id, played_on, MIN(played_on) OVER (PARTITION BY player_id) AS first_day FROM QuizPlay)
SELECT ROUND(SUM(DATEDIFF(played_on, first_day) = 1) / COUNT(DISTINCT player_id), 2) AS next_day_rate
FROM firsts