Arcade Coins Balance Over Time

An arcade app records each player's net coins for every day they play. The net can be negative on days a player spends more than they win.

For every row in arcade_sessions, show the player's cumulative net coins from their first day up to and including that day. Return player_id, played_on and coins_to_date, ordered by player_id ascending, then played_on ascending. If the table is empty, return no rows.

Tables

arcade_sessions — One row per player per day played.

Example

arcade_sessions

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Window Functions · problem 35 of 70 · SQLite

The idea

A running total is SUM used as a window function with an ordering. PARTITION BY player_id restarts the total for each player, and ORDER BY played_on inside OVER makes the frame run from the player's first row up to the current row. Without that inner ORDER BY, the frame is the whole partition and every row shows the same grand total.

ISO date text sorts in calendar order, so ordering by played_on directly is safe. There is one row per player per day (the primary key), so the default frame, which includes rows tied on the ordering value, never double counts.

The alternative uses a correlated subquery: for each row, add up that player's coins on or before that day. It is easier to read for beginners but scans the player's rows again for every row, so the window version scales better.

Solution

SELECT
  player_id,
  played_on,
  SUM(coins) OVER (
    PARTITION BY player_id
    ORDER BY played_on
  ) AS coins_to_date
FROM arcade_sessions
ORDER BY player_id, played_on;

Another way

SELECT
  s.player_id,
  s.played_on,
  (SELECT SUM(e.coins)
   FROM arcade_sessions AS e
   WHERE e.player_id = s.player_id
     AND e.played_on <= s.played_on) AS coins_to_date
FROM arcade_sessions AS s
ORDER BY s.player_id, s.played_on;

Why common answers fail

Leaves out PARTITION BY, so the running total mixes coins from all players.

SELECT player_id, played_on, SUM(coins) OVER (ORDER BY played_on) AS coins_to_date
FROM arcade_sessions ORDER BY player_id, played_on;

Leaves out ORDER BY inside OVER, so every row shows the player's grand total instead of a running total.

SELECT player_id, played_on, SUM(coins) OVER (PARTITION BY player_id) AS coins_to_date
FROM arcade_sessions ORDER BY player_id, played_on;

Orders the window by coins instead of by date, so the total accumulates in the wrong sequence.

SELECT player_id, played_on, SUM(coins) OVER (PARTITION BY player_id ORDER BY coins) AS coins_to_date
FROM arcade_sessions ORDER BY player_id, played_on;

What the interviewer is checking

A warm-up that checks you know how PARTITION BY and ORDER BY inside OVER change what SUM returns.

Mistakes to avoid

• Forgetting PARTITION BY.
• Forgetting ORDER BY inside OVER.
• Expecting the window ORDER BY to sort the output.

Follow-ups to expect

• Show each player's lowest balance ever (MIN over the running total per player).
• If a player could have several rows per day, how would the default RANGE frame behave? (Same-day rows would share one total; use ROWS with a tie-breaker or aggregate per day first.)
• A 3-session rolling total (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).

In real work

Wallet balances, cumulative signups and year-to-date revenue are all per-entity running totals.

Practice next