Game Play Analysis IV SQL Query | SQL Interview Question

Problem statement

Prompt

Given the activity table below (one row per (player, event_date)), write a query that reports the fraction of players who logged in on the day after their first login.

A player "returns" if they have an event_date exactly one day after their first event_date (their minimum date).
Output a single column fraction rounded to 2 decimal places (ROUND(..., 2)).

Example

player_id	device_id	event_date	games_played
1	2	2024-03-01	5
1	2	2024-03-02	6
2	3	2024-03-02	1
3	1	2024-03-04	0

Player 1 returns (Mar 2), player 2 does not, player 3 does not → 1/3 ≈ 0.33.

Expected result:

fraction
0.33

Constraints

The primary key is (player_id, event_date).
A player can have several events; only the day after their _first_ counts.

Tips

Compute each player's first date with MIN(event_date) OVER (PARTITION BY player_id), then check whether event_date = first_date + 1 day exists for that player (julianday(event_date) - julianday(first_date) = 1).
Use a DISTINCT player list and a LEFT JOIN/EXISTS against the next-day condition.

Schema

CREATE TABLE activity (
    player_id    INTEGER NOT NULL,
    device_id    INTEGER NOT NULL,
    event_date   DATE NOT NULL,
    games_played INTEGER NOT NULL,
    PRIMARY KEY (player_id, event_date)
);

Sample data

INSERT INTO activity (player_id, device_id, event_date, games_played) VALUES
  (1, 2, '2024-03-01', 5),
  (1, 2, '2024-03-02', 6),
  (2, 3, '2024-03-02', 1),
  (3, 1, '2024-03-04', 0),
  (3, 1, '2024-03-05', 0),
  (4, 4, '2024-03-06', 1);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: fraction of players active the day after their first login.
WITH first_logins AS (
    SELECT player_id, MIN(event_date) AS first_date
    FROM activity
    GROUP BY player_id
),
returned AS (
    SELECT DISTINCT f.player_id
    FROM first_logins f
    JOIN activity a
      ON a.player_id = f.player_id
     AND julianday(a.event_date) - julianday(f.first_date) = 1
)
SELECT ROUND(
           1.0 * (SELECT COUNT(*) FROM returned) /
           (SELECT COUNT(*) FROM first_logins),
           2
       ) AS fraction;

Key concepts

MIN over partition
julianday
LEFT JOIN
Percentage

Game Play Analysis IV