Game Play Analysis II

Problem

Table: Activity(player_id INT, device_id INT, event_date DATE, games_played INT). The pair (player_id, event_date) is the primary key. Each row is one login session: a player, on some device, on some date, playing some number of games. Report the device each player first logged in with.

Mechanism in one sentence

FIRST_VALUE(device_id) OVER (PARTITION BY player_id ORDER BY event_date) works because adding ORDER BY to a window silently installs the default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — so for every row the engine looks back from the partition's start up to that row, and the first element of that window is always the chronologically earliest device for the player.

Worked example

Concrete input — note player 1 logged in twice on the same device, and player 3 switched devices two years apart:

The query

SELECT DISTINCT
       player_id,
       FIRST_VALUE(device_id) OVER (
           PARTITION BY player_id
           ORDER BY event_date
       ) AS device_id
FROM Activity;

Step 1 — what the window function emits, row by row

The window function does not collapse rows; it appends a computed column to every input row. Within each player_id partition the rows are sorted by event_date, then for each row FIRST_VALUE reaches back to the start of the frame and returns the device from the earliest date. Because the frame's lower bound is fixed at the partition start, every row in a partition gets the same first device:

Five input rows in, five rows out — the result is still row-per-session, just with a repeated device_id column.

Step 2 — DISTINCT collapses the duplicates

After the window pass, every partition emits its first device repeated once per session row. We only project player_id and the computed device_id, then DISTINCT dedupes identical pairs. Player 1's two rows are both (1, 2) and collapse to one; player 3's two rows are both (3, 1) and collapse to one:

Why not GROUP BY device_id?

The instinct SELECT player_id, device_id FROM Activity GROUP BY player_id (or with MIN(device_id)) is wrong: MIN(device_id) returns the smallest device number, not the device tied to the earliest date. For player 3 that would return device 1 by luck (1 < 4), but flip the input so the first login was on device 9 and a later login on device 2, and MIN answers 2 — the wrong device. You must order by event_date, never by the device id.

Three correct shapes — and their cost

All three return the right answer; they differ in how much work the engine does.

1. FIRST_VALUE + DISTINCT (the page's approach)

SELECT DISTINCT player_id,
       FIRST_VALUE(device_id) OVER (PARTITION BY player_id ORDER BY event_date) AS device_id
FROM Activity;

Computes the window over all N rows, then deduplicates. The window touches every session even though you keep one row per player — wasted work on heavy partitions, and DISTINCT adds a sort/hash pass on top.

2. ROW_NUMBER() = 1 (usually the cheapest, clearest)

SELECT player_id, device_id
FROM (
    SELECT player_id, device_id,
           ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS rn
    FROM Activity
) t
WHERE rn = 1;

Same single window pass, but the WHERE rn = 1 filter throws away all but one row per player without a separate DISTINCT step. This is the canonical "first/last per group" pattern and the one to reach for by default.

3. Correlated subquery on MIN(event_date)

SELECT player_id, device_id
FROM Activity a
WHERE event_date = (
    SELECT MIN(event_date) FROM Activity b WHERE b.player_id = a.player_id
);

No window functions — portable to ancient engines (pre-8.0 MySQL). Reads clearly, but can re-scan the table per player without a good index on (player_id, event_date).

Pitfalls

• Forgetting that the default frame is asymmetric. With ORDER BY present, the implicit frame is UNBOUNDED PRECEDING .. CURRENT ROW, not the whole partition. FIRST_VALUE happens to be frame-insensitive here (its result equals the partition start), but LAST_VALUE(device_id) OVER (... ORDER BY event_date) is the classic trap: it returns the current row, not the last row of the partition, because the frame ends at CURRENT ROW. To get the true last you must write ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• Ties on event_date. If a player has two devices on the same earliest date, the order is non-deterministic; LeetCode's guarantee that (player_id, event_date) is unique saves you here, but real schemas rarely do — add a tiebreaker like ORDER BY event_date, device_id.
• DISTINCT over a window is silently expensive. The window is materialized across every row first, then deduped. On a player with 10,000 sessions you compute 10,000 FIRST_VALUEs to keep one. ROW_NUMBER() = 1 avoids the second pass.
• Putting DISTINCT in the wrong place. DISTINCT applies to the whole projected row, so you must project only player_id and the first device. If you accidentally also select event_date or games_played, the rows stop being duplicates and DISTINCT no longer collapses them.

Takeaways

• An ORDER BY inside OVER() turns on a default frame of UNBOUNDED PRECEDING .. CURRENT ROW — internalize this or LAST_VALUE will bite you.
• FIRST_VALUE is frame-robust for "first"; LAST_VALUE is not — name the full frame when you want the partition's last row.
• For "the row with the min/max per group," prefer ROW_NUMBER() OVER (...) = 1: one pass, no extra DISTINCT sort.
• Order by the column that defines "first" (event_date), never by the value you're returning (device_id) — that is why MIN(device_id) is wrong.

Based on LeetCode 512 "Game Play Analysis II". Window-frame semantics per the ISO SQL standard and the PostgreSQL documentation on window functions and the default RANGE UNBOUNDED PRECEDING frame; the LAST_VALUE default-frame caveat and the ROW_NUMBER()=1 top-per-group pattern are standard practitioner knowledge (Markus Winand, Use The Index, Luke; PostgreSQL/MySQL 8.0 manuals). Re-authored and deepened for this guide — added the row-by-row frame trace, the diagram, the MIN(device_id) counter-example, and the three-approach cost comparison.