Cumulative Sum

Hook: Interviewers love this because it turns a plain total into a live scoreboard that grows row by row.

Question: How do you calculate a cumulative sum in SQL?

Answer: Use a window function, which is a calculation that looks at a set of related rows without collapsing them into one row. The usual pattern is SUM(amount) OVER (ORDER BY ...), and you add PARTITION BY when you want the total to restart for each group. For the safest row-by-row running total, spell out a frame like ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Interview-Ready Answer: I’d calculate a cumulative sum with SUM(amount) OVER (...). If I want the running total across the whole result, I order the rows and use an explicit window frame; if I want it per customer, account, or day, I add PARTITION BY. One important detail is to prefer ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when I need true row-by-row behavior, because duplicate sort keys can otherwise give surprising results.

What a cumulative sum really is

A cumulative sum is just a running total. If your amounts are 10, 20, 30, the cumulative sum becomes 10, 30, 60. The key SQL idea is that you want to keep every original row, but let each row see all earlier rows in a defined order.

How SQL computes it under the hood

1. SQL forms the working set from your query result, after filters like WHERE.
2. Rows are split into partitions if you use PARTITION BY. A partition is just a subgroup that gets its own running total.
3. Each partition is ordered by the columns in the window ORDER BY. This order matters; running totals are not meaningful without a sequence.
4. The database scans in order and keeps an accumulator, which is a simple variable holding the current total.
5. Each row is emitted with its own total, so you get one output row per input row, not one row per group.

Why window functions beat GROUP BY

GROUP BY collapses rows; window functions preserve them. That is the whole reason cumulative sums exist in window form.

Performance and complexity

Most engines must sort each partition, so the practical cost is usually O(n log n) because of sorting, followed by an O(n) scan for the running total. If the data is already read in the right order through an index, the database may avoid or reduce the sort. Memory matters too: for example, PostgreSQL uses work_mem for sort and window operations, so large partitions can spill to disk and slow down.

Important edge cases

• Duplicate order keys: Many engines default to a peer-based frame when you write ORDER BY inside the window. That can make rows with the same sort key share the same total. If you need a true row-by-row running total, use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and add a tie-breaker column like an ID.
• NULL amounts: SUM ignores NULL, but if every value in the frame is NULL, the result is NULL. If business rules say missing means zero, wrap the value in COALESCE(amount, 0).
• Partitions: Without PARTITION BY, the cumulative sum runs across the entire result set. With it, the total resets per group.
• Descending order: You can compute a reverse running total by sorting descending. The meaning changes, so make sure that is what the business wants.

Simple mental model

Think of a cumulative sum like a backpack that keeps getting heavier as you walk through the rows. Each new row adds one more item, and the backpack weight is the running total for that row.

Real-World Story: Imagine an ecommerce checkout service building a finance dashboard for daily revenue. The product team wants each transaction row plus a running total for the day so they can spot whether sales are accelerating or flat.

The engineer writes a window query and shows cumulative revenue by purchase time. That works until two orders arrive with the exact same timestamp. If the query uses the wrong window frame or no tie-breaker, the running total can jump in a way that looks impossible to finance users. The dashboard may not throw an error, but the chart can show flat steps, repeated totals, or totals that disagree with the exported CSV.

What goes wrong: a developer uses GROUP BY to get daily totals, then tries to chart it as if it were a running total. The page only has one row per day, so the chart loses transaction-level detail. Symptoms include support tickets like “why does this day look like one giant jump?”, audit logs showing no SQL error, and analysts manually recomputing totals in spreadsheets. The business impact is bad: reporting distrust, delayed reconciliation, and time wasted debugging a query that was logically correct for totals but wrong for a running total.

Follow-up & Tricky Questions:

• How do you reset the cumulative sum for each customer? Add PARTITION BY customer_id so each customer gets its own running total. The window restarts at zero for every partition.
• What is the difference between ROWS and RANGE? ROWS counts physical rows, while RANGE groups peers with the same sort key value. For duplicate timestamps or dates, ROWS is usually the safer choice for a true row-by-row total.
• Why is my cumulative sum wrong when two rows have the same timestamp? Because the sort key is not unique, the order among peers may be ambiguous. Add a tie-breaker such as an ID or use an order that is uniquely sortable.
• Can I calculate a reverse running total? Yes, order descending and use the same window logic. Just remember the meaning changes: you are now accumulating from the latest row backward.
• Does ORDER BY in the outer query affect the window calculation? No. The window function uses its own ORDER BY; the outer ORDER BY only changes display order after the calculation is done.
• Tricky: does SUM return zero for an all-NULL frame? No, it returns NULL when there are no non-NULL values to sum. Use COALESCE if your business rules want zero instead.
• Tricky: do I need a subquery for a cumulative sum? Usually no. The window function can compute it directly in one query, which is cleaner and often faster than correlated subqueries or self-joins.

Common Mistakes:

• Using GROUP BY instead of a window function. GROUP BY removes the original rows; use SUM(...) OVER (...) when you need both detail rows and the running total.
• Forgetting ORDER BY inside the window. Without an order, a cumulative sum has no sequence and becomes meaningless.
• Not using a tie-breaker for duplicate sort keys. Add a unique column like sale_id so row order is deterministic.
• Ignoring NULL handling. If missing values should count as zero, wrap them in COALESCE(amount, 0).

Memory Hook: “A cumulative sum is a backpack: every new row adds one more item, and the weight you feel is the running total.”

Cheat Sheet:

• SUM(amount) OVER (ORDER BY ...) = running total.
• PARTITION BY resets the total per group.
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives explicit row-by-row behavior.
• Duplicate sort keys need a tie-breaker.
• GROUP BY totals; window functions preserve detail rows.

Practice Tasks:

• Compute a running total of order amounts by order_date.
• Reset the running total per customer_id.
• Modify the query to show a reverse running total for the latest 5 rows.