Share of Quiz Players Who Returned the Next Day

Medium
21.

Share of Quiz Players Who Returned the Next Day

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

ColumnType
player_idint
played_ondate
scoreint

Primary key (together): player_id, played_on.

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

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

QuizPlay

player_idplayed_onscore
12025-04-2970
12025-04-3055
22025-04-3080
22025-05-0290
22025-05-0360
32025-04-3040
32025-05-0185
42025-05-0530

Output

next_day_rate
0.5
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.

Share of Quiz Players Who Returned the Next Day

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.

  • Difficulty: Medium
  • Topics: Aggregation, Dates, Subqueries
  • Dialect: MySQL
  • Problem: #21

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

ColumnType
player_idint
played_ondate
scoreint

Primary key: player_id, played_on.

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

Examples

Example 1

QuizPlay

player_idplayed_onscore
12025-04-2970
12025-04-3055
22025-04-3080
22025-05-0290
22025-05-0360
32025-04-3040
32025-05-0185
42025-05-0530

Output

next_day_rate
0.5

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

← Average Selling Price of Each Canteen Item · Students Who Attended Every Fest Workshop →