Running Total

Hook: Interviewers love this one because a running total looks simple, but it quietly tests whether you really understand ordering, window functions, and edge cases.

Question: How do you calculate a running total in SQL?

Answer: A running total is a total that grows row by row as you move through ordered data. In SQL, the cleanest way to do it is with a window function: SUM(...) OVER (ORDER BY ...). If you need the total to restart for each customer, account, or category, you add PARTITION BY.

Interview-Ready Answer: I would use a window function with SUM(...) OVER and an ORDER BY clause, because that gives me a cumulative total without collapsing the rows. If I need separate totals per group, I add PARTITION BY. One detail I always watch is the frame and tie order: for a true row-by-row running total, I prefer ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and a stable sort key.

What it is

Detailed Explanation: A running total is the sum of all values from the beginning of a sequence up to the current row. Think of it like a receipt tape: each new line adds to the total, but earlier lines stay visible. In SQL, this is usually done with a window function (a function that looks at a set of rows related to the current row, without grouping them away).

How it works under the hood

1. SQL first finds the rows you want and sorts them by the chosen order, such as date plus a tie-breaker id.
2. The window definition tells the database which rows belong to the moving frame for each current row.
3. SUM adds the values in that frame. As the current row moves forward, the frame grows from the first row to the current row.
4. If you use PARTITION BY, the running total restarts at each partition, such as each account or customer.
5. If you use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, the database computes a true row-by-row cumulative total, which is usually what interviewers want.

When and why to use it

• Balances: bank accounts, wallet balances, inventory on hand.
• Dashboards: revenue to date, signups to date, orders to date.
• Reports: totals that must stay visible on every row instead of collapsing into one summary row.

Window function vs older approaches

Performance and edge cases

Most databases do one sort plus one scan for the window calculation, so the cost is usually about O(n log n) because of sorting, with the cumulative step itself being close to O(n). On 100,000 rows this is usually fine; on millions of rows, the sort and memory use matter, so an index that matches the partition and order columns can help the planner read rows in order. A few important gotchas: if your order column has ties, rows may share the same value unless you add a tie-breaker; if values can be NULL, decide whether they should act like zero; and if the business meaning needs resets, use PARTITION BY rather than trying to fake it with a filter. Also remember that some systems default to a RANGE frame when you only say ORDER BY, which can group tied values together; ROWS is the safer choice for a strict running count of physical rows.

Real-World Story: Imagine a checkout service for an online store. The finance team wants a per-day cash balance for each merchant account, so every transaction row needs to show the balance after that payment, refund, or fee. A running total makes that report easy: order the ledger rows by timestamp, partition by account, and sum amounts as you go.

What goes wrong when people misunderstand it? A developer might group by day first and then try to compute the total, which removes the row-level detail. The dashboard then shows one number per day instead of a true cumulative line. In production, that looks like a line chart with sudden jumps, finance reconciliation mismatches, and logs where same-timestamp rows appear with the same total when they should not. Users notice that yesterday's balance does not match today's exported report, and support sees complaints like 'my refund is there, but the balance is off by 20 dollars.'

Follow-up & Tricky Questions:

Follow-up questions

• How do you restart the running total for each customer? Add PARTITION BY customer_id. That tells SQL to calculate a separate cumulative sum inside each customer group.
• What if two rows have the same date? Add a tie-breaker like an id or timestamp in the ORDER BY so the sequence is stable and repeatable.
• Can you do a running total without window functions? Yes, with a correlated subquery or self-join, but those are usually slower and harder to read on large tables.
• How do you make the first row show zero instead of NULL? Wrap the input in COALESCE(amount, 0) or wrap the final expression with COALESCE if your business rule wants zero for empty sums.
• Can running totals handle negative numbers? Yes; negatives simply subtract from the cumulative sum, which is exactly how refunds, reversals, and adjustments are usually modeled.

Tricky questions

• Why is ROWS sometimes better than just ORDER BY? Because some databases use a default frame that behaves like RANGE, which can treat tied order values as one peer group. ROWS forces row-by-row accumulation.
• What happens if you omit ORDER BY entirely? Then it is no longer a running total; you get the same total repeated for every row in the partition because there is no sequence to accumulate over.
• Does SUM include NULL values? No, SUM ignores NULL, but a frame containing only NULL values can still produce NULL, so make the business rule explicit with COALESCE when needed.

Common Mistakes:

• Sorting only in the outer query, not inside the window. Fix: the running total needs ORDER BY inside OVER.
• Forgetting PARTITION BY when totals must restart per group. Fix: partition by customer, account, or category as needed.
• Using a non-unique order column and getting unstable results. Fix: add a tie-breaker like an id or timestamp.
• Assuming NULL always means zero. Fix: decide the rule explicitly with COALESCE.

Memory Hook: Picture a receipt tape rolling across the table: each new line adds to the tape, but the old numbers stay visible. Running total = receipt tape sum.

Cheat Sheet:

• SUM(amount) OVER (ORDER BY ...) is the core pattern.
• PARTITION BY restarts the total for each group.
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives a strict row-by-row total.
• Add a tie-breaker in ORDER BY for duplicate dates or timestamps.
• Use COALESCE if missing amounts should count as zero.

Practice Tasks:

• Compute a running total of daily orders for one store.
• Restart the running total per customer and sort by purchase date.
• Add a refund row and confirm the balance goes down correctly.