Running Total

Hook: Interviewers love this question because it looks simple, but it quietly tests whether you understand ordered data, window functions, and one very common trap: duplicate sort keys.

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

Answer: A running total is a cumulative sum: each row shows the total of all rows before it plus the current row. In SQL, the cleanest way is a window function with SUM(...) OVER (...)`, usually with ORDER BY and often PARTITION BY if you want the total to restart for each group. The important detail is to use the right window frame so rows are accumulated one by one, not lumped together unexpectedly.

Interview-Ready Answer: I would use a window function, usually SUM(amount) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). That gives me a cumulative sum without collapsing the rows the way GROUP BY would. I also like to add a unique tie-breaker in the ORDER BY so the result is deterministic when multiple rows share the same date or timestamp.

What a running total really is

A running total is just a cumulative sum. If the amounts are 10, 7, and 3, the running total is 10, 17, and 20. In SQL, the key idea is that we want one output row per input row, but each row should be allowed to look backward at earlier rows in a chosen order. That is exactly what a window function does: it computes across a related set of rows while keeping each row visible.

How it works under the hood

1. The database starts with the rows you selected.
2. It sorts them by the ORDER BY clause inside the window, because a running total only makes sense in a defined sequence.
3. If you use PARTITION BY, the engine splits the data into separate buckets first, such as one bucket per customer or per store.
4. For each row, it builds a frame - the slice of rows the aggregate can see. For a running total, that frame usually starts at the first row in the partition and ends at the current row.
5. It computes SUM(amount) over that frame and emits the result next to the original row.
6. Then it moves to the next row. Good engines do not literally re-sum everything from scratch every time; they often reuse work after the sort, so the scan is effectively linear.

Why ROWS usually matters

The subtle part is the frame type. Many candidates write only ORDER BY and assume the database will behave like ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. In many engines, the default frame with ORDER BY behaves more like RANGE, which groups rows that tie on the ordering value. That can make two rows with the same date share the same running total, which is often not what you want.

Memory aid: think of ROWS as a staircase and RANGE as a landing. A staircase moves one step at a time; a landing jumps you across all equal-value peers at once.

When and why to use it

• Ledger-style data: balances, revenue, inventory movement, score history.
• Grouped totals: per customer, per account, per store, per day.
• Trend charts: dashboards that need cumulative lines instead of separate points.

Use PARTITION BY when the total must reset. For example, if you are tracking sales per store, each store should get its own running sum. Without partitioning, all stores get mixed into one long total.

Performance and alternatives

A good mental model is: sort first, then scan once. The sort is usually the expensive part, so the total cost is often dominated by sorting, roughly O(n log n), followed by an O(n) pass to accumulate values. If the rows are already read in the needed order, or an index matches PARTITION BY plus ORDER BY, the engine may do less work. On large tables, the sort can spill to disk if memory is too small; that is when a query that looks harmless suddenly gets slow.

Window functions are supported in modern versions of PostgreSQL, SQL Server, MySQL 8+, Oracle, and SQLite 3.25+. If you are on an older database, you may need a self-join or correlated subquery, but the window version is usually the right answer in interviews because it is clearer and more efficient.

Edge cases to remember

• Duplicate dates or timestamps: add a tie-breaker like an ID so the order is deterministic.
• Negative amounts: refunds and reversals should reduce the total naturally.
• NULL amounts: SUM ignores them, but COALESCE(amount, 0) makes the intent obvious.
• Missing ORDER BY: a running total needs order; without it, the concept breaks.
• Wrong frame: default peer grouping can surprise you if you expected row-by-row accumulation.

Real-World Story: Imagine a checkout service for an online store that shows live revenue per store on a manager dashboard. Each payment event arrives with a store ID, timestamp, and amount, and the dashboard needs a growing sales total every minute. One team wrote a query with only ORDER BY event_time, and when two payments landed in the same second, the totals appeared to jump in awkward chunks instead of increasing row by row.

What went wrong showed up fast: finance noticed mismatched totals between the dashboard and the ledger, support saw screenshots where two same-second transactions displayed identical cumulative values, and logs showed duplicate timestamps in bursts during peak traffic. The fix was to use a deterministic order such as ORDER BY event_time, event_id and an explicit row frame so the running total advanced one payment at a time. That kind of bug is especially nasty because the data is not missing - it is just being summarized with the wrong mental model.

Follow-up & Tricky Questions:

• How do I reset the running total for each customer? Add PARTITION BY customer_id. That creates an independent cumulative sum inside each customer group.
• What is the difference between ROWS and RANGE? ROWS counts physical rows one by one, while RANGE includes all peers with the same sort value in the current frame.
• Can I calculate a running total without a subquery? Yes - that is exactly what a window function does. It keeps the original rows and adds the cumulative result beside them.
• How do I make the order deterministic when timestamps repeat? Add a unique tie-breaker like txn_id or created_at, id. Otherwise the database may not guarantee the order among tied rows.
• How would you do a moving total for the last 7 rows or 7 days? Use a bounded frame such as ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for rows, or a time-based frame where your database supports it.
• Tricky: If I write only SUM(amount) OVER (ORDER BY day), is that always row-by-row? No. Many databases use a peer-aware default frame, so duplicates can share the same cumulative value. Be explicit with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• Tricky: Does GROUP BY solve this? No. GROUP BY collapses rows into one result per group, while a running total must preserve every row.
• Tricky: Is a running total always safe on old database versions? Not always. Older systems may not support window functions at all, so you would fall back to a self-join or correlated subquery, usually with worse performance.

Common Mistakes

• Using GROUP BY instead of a window function - This collapses rows. Fix: use SUM(...) OVER (...) so each original row stays visible.
• Forgetting ORDER BY - A running total has no meaning without sequence. Fix: order by a column that reflects time or business order.
• Not specifying ROWS - Duplicate sort values can get grouped together. Fix: spell out ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• Ignoring tie-breakers - Equal timestamps can produce unstable output ordering. Fix: add a unique ID to the window order.

Memory Hook

Think of a running total like a shopping cart belt: every new item stays on the belt, and the number shown at each point is the total of everything already passed. ROWS moves item by item; RANGE can sweep all identical items onto the same spot.

Cheat Sheet

• Use SUM(amount) OVER (...) for cumulative totals.
• Add PARTITION BY to restart totals per group.
• Add ORDER BY to define the sequence.
• Prefer ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for row-by-row behavior.
• Add a unique tie-breaker to avoid nondeterministic ordering.
• Expect sort cost; the cumulative scan itself is cheap.

Practice Tasks

• Compute a running total of daily sales for one store.
• Reset that total per store using PARTITION BY.
• Rewrite it with a duplicate date and observe how ROWS and RANGE differ.