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". Free accounts get 10 free challenge solves, and this can be one of them.
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
Read the concept: Window functions tutorial
The trap to watch for: BETWEEN on timestamps
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.
Preparing for Capital One? How the CodeSignal data analyst assessment works.
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