Write a query to calculate a running total of daily sales.
SHORT ANSWER
Use SUM(amount) OVER (ORDER BY day) to get a cumulative sum. Add PARTITION BY to restart the total per group, and use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to be explicit.
Explain it simply
A running total is like a passbook balance: each line shows the sum of everything up to that line. In SQL, you ask for a sum over the rows from the start up to the current row.
Line by line
Table sales(day, amount).
| day | amount |
|---|---|
| 2026-01-01 | 100 |
| 2026-01-02 | 50 |
| 2026-01-03 | 70 |
sql
SELECT day, amount,
SUM(amount) OVER (
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM sales
ORDER BY day;Result: running_total is 100, 150, 220.
ORDER BY dayfixes the sequence.- The frame
UNBOUNDED PRECEDING AND CURRENT ROWmeans "from the first row to this one". - Without the explicit frame, the default is
RANGE, which treats rows with equaldayvalues as peers and gives them the same total.ROWSavoids that surprise. - For a total per city, write
PARTITION BY citybefore ORDER BY.
Before window functions, this needed a self join or a correlated subquery, which is much slower.
Complexity
Time: O(n log n) for the sort, then O(n) for the pass. Space: O(n).
What to say in the interview
"SUM() OVER with ORDER BY and a ROWS frame gives a cumulative total in one pass. I specify ROWS explicitly so ties on the order column do not share a value."