Game Play Analysis III

A windowed SUM(games_played) OVER (PARTITION BY player_id ORDER BY event_date) turns each row into a running total because the ORDER BY inside the window silently attaches a frame — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — so the engine re-aggregates over “every earlier row of this player plus me,” row by row, without ever collapsing the rows the way GROUP BY would.

Problem

Table Activity with primary key (player_id, event_date):

+--------------+------+
| Column Name  | Type |
+--------------+------+
| player_id    | int  |
| device_id    | int  |
| event_date   | date |
| games_played | int  |
+--------------+------+

For each player and each date they were active, report games_played_so_far — the cumulative number of games that player has played up to and including that date. This is LeetCode 534. Note the shape: the answer has the same number of rows as the input (one per activity record), each annotated with a total. That “same row count” is the tell that you want a window function, not a GROUP BY.

The query

SELECT player_id,
       event_date,
       SUM(games_played) OVER (
           PARTITION BY player_id
           ORDER BY event_date
       ) AS games_played_so_far
FROM Activity;

The OVER (...) clause is the whole mechanism. Read it as three knobs:

• PARTITION BY player_id — reset the running total at each new player. Without it, the sum would bleed across players into one global cumulative count.
• ORDER BY event_date — define the order in which rows accumulate. This also implicitly creates a frame. Drop the ORDER BY and you get a different query entirely (see Pitfalls).
• The frame (implicit here) — with an ORDER BY and no explicit frame clause, the SQL standard defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: the window for each row spans from the first row of the partition through the current row.

Worked trace

Take player 1 with three activity rows and player 2 with one. The engine processes one partition at a time, walks rows in event_date order, and for each row sums games_played over the frame “start-of-partition → current row.”

Each row keeps its identity — player 1 still produces three output rows — but the games_played_so_far column carries the prefix sum. Player 2’s zero-game day correctly reports 0, not NULL: a row with games_played = 0 is still inside the frame.

Window vs. GROUP BY — why not just aggregate?

A natural wrong instinct is SELECT player_id, SUM(games_played) ... GROUP BY player_id. That answers a different question. GROUP BY collapses each player’s rows into one total; you lose event_date and you get one number per player, not a per-date prefix. The window function keeps every row and computes a partial sum positioned at that row’s date.

Cost and indexing

The planner satisfies the window in roughly three steps: (1) sort or hash to group rows by player_id, (2) sort within each partition by event_date, (3) stream the partition once, maintaining a running accumulator. With the default UNBOUNDED PRECEDING .. CURRENT ROW frame the accumulator never has to look backward or recompute — it just adds the current row to a carried sum, so the streaming pass is O(n) after the sort, and the sort dominates at O(n log n).

• A composite index on (player_id, event_date) — which is exactly this table’s primary key — lets the engine read rows already in partition+order, often eliminating the sort entirely. This is why the PK ordering matters and is not a coincidence.
• Watch the frame mode. RANGE (the default) treats rows with equal event_date as peers — they all get the same cumulative value. ROWS would advance row by row even among ties. Here the PK guarantees no two rows share (player_id, event_date), so RANGE and ROWS produce identical results — but on a table with duplicate dates they diverge, and RANGE can also be measurably slower because it must look ahead for peer ties.

Pitfalls

• Dropping ORDER BY turns the running total into a partition-wide total. SUM(games_played) OVER (PARTITION BY player_id) with no ORDER BY has the frame RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — every row gets the player’s grand total (12, 12, 12), not 5, 11, 12. This is the single most common silent bug here. Why the naive version is wrong: no ordering means there is no “up to this row,” so the engine sums the entire partition for every row.
• The self-join alternative is correct but quadratic. SUM(b.games_played) FROM Activity a JOIN Activity b ON b.player_id = a.player_id AND b.event_date <= a.event_date GROUP BY a.player_id, a.event_date gives the same answer, but it materializes O(rows²) join pairs per player and is dramatically slower at scale. Reach for the window function unless you’re on a SQL engine with no window support.
• Ties under RANGE can surprise you. If your real table allowed two rows on the same date, the default RANGE frame gives both the same cumulative value (it includes all peers), which is often not what an analyst eyeballing “running total” expects. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when you want strict row-by-row accumulation.
• NULL games vs. zero games. SUM ignores NULLs, so a NULL games_played day contributes nothing and inherits the previous total; a 0 day also adds nothing but is a legitimate row that must still appear. The spec says “possibly 0,” so the data uses 0, and the output must include those zero-game days — do not filter them with WHERE games_played > 0.
• Selecting non-window columns without including them. Because there is no GROUP BY, you may select any column (e.g. device_id) freely — but that’s a trap: a player can use different devices on different dates, so device_id is ambiguous to a reader even though the engine accepts it. Select only what the question asks for.

Takeaways

• A running total is SUM(x) OVER (PARTITION BY key ORDER BY seq) — the ORDER BY inside OVER is what installs the default UNBOUNDED PRECEDING .. CURRENT ROW frame that makes it cumulative.
• Same row count out as in ⇒ window function; collapsed to one-per-group ⇒ GROUP BY. Choosing wrong loses either the per-date detail or the running shape.
• An index matching (PARTITION BY cols, ORDER BY cols) — here the PK (player_id, event_date) — can erase the sort and make the whole thing a single streaming pass.
• Know RANGE vs ROWS: identical when the ordering key is unique, divergent (and RANGE slower) the moment ties exist.

Re-authored and deepened for this guide. Based on LeetCode 534 “Game Play Analysis III.” Window-frame semantics (the implicit RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW default, and RANGE vs ROWS peer behavior) follow the SQL:2003 standard as documented in the PostgreSQL manual, “Window Function Calls” and Section 3.5 “Window Functions,” and Markus Winand’s SQL Performance Explained / use-the-index-luke.com on indexing window ORDER BY. Opaque alt="Image" raster figures replaced with hand-authored inline SVGs; rote per-clause bullets replaced with a traced example, a GROUP BY contrast, and a cost/index discussion.