How do you compute a running total (cumulative sum) using a window function, and what frame clause does it use by default?
Use SUM() OVER (ORDER BY ...) — the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This works correctly for strictly increasing order columns, but silently over-counts when multiple rows share the same ORDER BY value because the RANGE default includes all peers.
How to think about it
A running total is the gateway window function — interviewers use it to check that you understand the frame clause, not just the syntax. The follow-up is always “what happens when two rows share the same ORDER BY value?”, so address that head-on.
The pattern is SUM(amount) OVER (ORDER BY ...) with an explicit ROWS frame, and PARTITION BY to restart the total per group. The explicit frame is what gives you the classic staircase, processing each physical row exactly once.
A worked example — partitioned running total
Reset the cumulative sum per customer; customer 1 has two orders on the same date:
SELECT customer_id, order_date, amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total_rows
FROM orders
ORDER BY customer_id, order_date, id;| customer_id | order_date | amount | running_total_rows |
|---|---|---|---|
| 1 | 2024-01-01 | 100 | 100 |
| 1 | 2024-01-02 | 50 | 150 |
| 1 | 2024-01-02 | 80 | 230 |
| 2 | 2024-01-01 | 200 | 200 |
| 2 | 2024-01-03 | 120 | 320 |
Two things to notice. The total resets at customer 2 (back to 200, not continuing from 320) — that’s PARTITION BY doing its job. And customer 1’s two Jan-02 orders staircase cleanly to 150 then 230, because of the explicit ROWS frame. Drop that frame and the default RANGE would give both Jan-02 rows the same 230 — the tell-tale “stalled” running total.
The frame options at a glance
| Frame keyword | Meaning |
|---|---|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | partition start up to this physical row (running total) |
ROWS BETWEEN N PRECEDING AND CURRENT ROW | current row plus N before it (moving window) |
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | up to and including all peers on the ORDER BY value |
A moving average is the same machine with a bounded frame: AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) gives a 7-row look-back (6 before + current), and early rows simply use however many rows exist.