SQL Quest › SQL Interview Questions › Window Functions
Running Total of Daily Card Spend
Cumulative spend, the shape every finance deck opens with. The trap is the grain. A SUM(amount) OVER (ORDER BY txn_at) straight off the transactions table gives you a running total after every single swipe — 2,165 rows, one per transaction, and nobody asked for that. Aggregate to one row per day first, in a CTE, then run the window over the daily series.
Return day (date(txn_at)), daily_spend (that day's SUM of amount, rounded to 2 decimals) and running_total (every day up to and including this one, rounded to 2 decimals). 61 rows — the ledger runs 2026-03-04 to 2026-05-03. Order by day ascending.
The frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is also the default when you write OVER (ORDER BY day) with no frame, but write it out: the default for a window with an ORDER BY is RANGE, not ROWS, and on a column with duplicates the two disagree.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
transactions
| txn_id | account_id | amount | txn_at | merchant_id | lat | lng | status |
|---|---|---|---|---|---|---|---|
| 1 | 149 | 151.84 | 2026-03-04T12:39:39.078Z | 24 | 40.342 | -74.4092 | completed |
| 2 | 21 | 10.72 | 2026-03-04T13:04:50.641Z | 7 | 52.9366 | 13.3229 | completed |
| 3 | 35 | 158.78 | 2026-03-04T13:27:15.528Z | 25 | 52.7397 | 13.3134 | completed |
Expected output: 2026-03-04 2011.18 2011.18; 2026-03-05 4036.08 6047.26; 2026-03-06 5521.4 11568.66; ...
Hint
SELECT, Window Functions, Frame Clause, and open the hint there if you stall.Concepts
SELECT Window Functions Frame Clause CTE Date Functions SUM Window Functions + CTE
Practise the topic: SQL practice questions · Window function practice · CTE practice · Date function practice · GROUP BY exercises
In these company practice sets
Capital One · Stripe · Goldman Sachs · Bloomberg
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test