Running Tab

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Problem

When a customer buys more than once on the same day, each of those purchases should show the same figure: everything the customer has spent through the end of that day. A finance analyst wants every purchase in transactions listed next to that spend-to-date figure for its customer.

Every purchase adds to the total. Watch the tab grow.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

This is a running balance dressed up as a finance report. The skill is carrying a per-customer spend-to-date onto every purchase row without losing a single row. Anyone can total spend per customer. What separates candidates is keeping all 200 rows, resetting the tally for each customer, and noticing that two purchases on the same day share one figure. Collapse the rows and finance gets 15 totals instead of 200 balances. Step strictly row by row and one of user 1167's two same-day purchases reports a balance that leaves out the other.

Three decisions

Step 1: Keep one row per purchase

The answer has as many rows as transactions: 200. That rules out GROUP BY user_id, which folds each customer into one row and throws away transaction_date and total_amount. You need an aggregate computed per row that leaves the rows alone, SUM(total_amount) OVER (...), or an equivalent correlated sum of each customer's earlier purchases.

Step 2: Reset the tally with PARTITION BY user_id

Without the partition the sum runs across the whole table, so customer 197's balance starts with everything customer 100 spent. The partition is what makes the figure a customer's own spend.

Step 3: Order by transaction_date and keep the default frame

Once the window has an ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: every row dated on or before the current one, peers included. Both of user 1167's same-day purchases therefore get the full day's total, which is exactly 'spent through the end of that day'.

SELECT
  user_id,
  transaction_date,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY user_id
    ORDER BY transaction_date
  ) AS cumulative_spend
FROM transactions

SELECT user_id, SUM(total_amount) AS total_spend
FROM transactions
GROUP BY user_id

Strictly row by row

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives each same-day purchase its own figure. With transaction_date as the only sort key, the engine decides which of the two comes first, so one balance omits the other and the answer can change between runs.

By date (the default)

The default RANGE frame treats same-date rows as peers. Both rows report the day's closing spend, the result is deterministic, and it matches a reconciliation done by date.

Common follow-up questions

• How would you show each customer's balance before the current purchase instead of after it? (Tests frame control: `ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING`, or subtracting `total_amount`, and what same-day peers do to each.)
• Finance now wants the balance to restart every calendar year. What changes? (Tests adding the year of `transaction_date` to the partition key.)
• If same-day purchases must get distinct balances in a stable order, what do you add? (Tests a `ROWS` frame with `transaction_id` as a tiebreaker in the window's `ORDER BY`.)
• `transaction_date` is stored as text. When would that break the order of accumulation? (Tests knowing that ISO dates sort chronologically as strings and other formats do not.)