Running Balance

Hook: Interviewers love this one because it looks simple, but it quietly checks whether you can turn raw transactions into an ordered story without making the math lie.

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

Answer: A running balance is the total after each row, based on all earlier rows in the correct order. In SQL, the cleanest way is a window function: SUM(amount) OVER (...) . You usually partition by account, order by time plus a unique tie-breaker, and, if needed, add an opening balance once at the start.

Interview-Ready Answer: I’d use a windowed SUM to compute the balance row by row. I partition by account, order by transaction time plus a unique id so the order is deterministic, and I use a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame so each row includes every prior transaction. If there is an opening balance, I add it once at the end. The main detail I’d call out is that ordering only by timestamp can be wrong when two transactions happen at the same time.

What a running balance really means

Detailed Explanation: Think of a ledger like a receipt tape. Every new line changes the total, but only if you read the lines in the right order. A running balance is just the cumulative total after each row, often per account, user, or wallet.

1. Start with the base amount. If there is an opening balance, that is your starting point. If not, start from zero.
2. Pick the right grouping. Use PARTITION BY account_id when each account needs its own balance.
3. Pick the right order. Use a real business order such as transaction timestamp, then add a tie-breaker like txn_id so two rows at the same time cannot swap places.
4. Accumulate rows. SUM(amount) OVER (...) tells the database to keep adding the current row to all earlier rows in that partition.
5. Make the frame explicit. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means: from the first row in the partition up to this row, one row at a time.

Why the window function is the best default

Performance and under-the-hood behavior

Most databases sort each partition first, then scan it once to keep the running total. That usually means the query is about O(n log n) because of sorting, then O(n) for the accumulation. On a million-row ledger, that is normal and efficient; on a 50-million-row table, the sort and memory use become noticeable. A helpful index is often something like (account_id, txn_ts, txn_id), because it matches the partition and order.

One subtle gotcha: if you write only ORDER BY txn_ts, many engines treat equal timestamps as peers. With the default frame in some systems, peer rows can share the same frame, which makes the result less intuitive. For running balances, the safest habit is to be explicit with ROWS and include a unique tie-breaker.

Edge cases to remember

• NULL amounts: SUM ignores nulls, but if every value in a partition is null, the result can be null, so COALESCE is useful.
• Negative amounts: refunds, fees, and reversals should reduce the balance naturally.
• Multiple accounts: always partition by the account key unless you intentionally want one global ledger.
• Timezone/order issues: if timestamps come from different timezones, normalize them first or the order can be wrong.

Real-World Story: Imagine a wallet service inside a checkout app. Every purchase, refund, service fee, and manual adjustment must update the customer’s balance in the exact order it happened. The finance team uses the running balance to reconcile statements, and support uses it to explain why a user is short by a few cents.

What goes wrong when people misunderstand this? A team once ordered only by timestamp, and two same-second transactions flipped depending on the plan chosen by the database. In the UI, some users saw the balance jump by a few cents between refreshes. In logs, the reconciliation job reported mismatches like ‘ledger total does not match statement total’, and support tickets mentioned duplicate-looking charges even though the real bug was ordering, not billing.

The symptom is usually not a crash. It is worse: silent financial drift, failed audits, angry users, and a painful backfill job to recompute every balance with the correct order. That is why engineers care so much about deterministic ordering and explicit window frames.

Follow-up & Tricky Questions:

• How do you calculate a running balance per customer? Partition by the customer key, then order each customer’s rows independently. The same window function works; only the partition changes.
• How do you include an opening balance? Add it as a separate value or join it from a balance table, then add the cumulative transaction sum on top. That keeps the first row correct.
• How do you show running balance by day instead of by transaction? First aggregate transactions per day, then run the window sum over the daily totals. That gives one line per day, not one line per transaction.
• What if transactions can arrive late? Recompute from the affected point forward, because running totals depend on order. In production, many teams store the raw ledger and derive the balance when needed.
• Can you do this without window functions? Yes, but usually with more code and worse performance. A self join or recursive CTE can work when the database is old or the logic is highly custom.
• Tricky: Is ORDER BY in the outer query enough? No. The ordering inside OVER (...) defines the math; outer ORDER BY only changes display order.
• Tricky: Does SUM(amount) OVER (ORDER BY txn_ts) always behave like a row-by-row running total? Not necessarily. Duplicate timestamps can form peers, so use ROWS and a tie-breaker to make the result deterministic.
• Tricky: Do NULL amounts count as zero? SUM ignores null inputs, but if all rows in a frame are null the result can still be null. Use COALESCE if you need a guaranteed numeric answer.

Common Mistakes:

• Forgetting the partition. Correction: add PARTITION BY account_id unless one global balance is truly intended.
• Using only a timestamp for order. Correction: add a unique tie-breaker like txn_id so same-time rows never swap.
• Assuming the default frame is always row-by-row. Correction: write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly.
• Ignoring the opening balance. Correction: seed the running total with the starting amount, then add the transaction sum on top.

Memory Hook: Think of a running balance as a receipt tape: every line is added in order, and if two lines share the same time, you still need a unique line number to keep the tape honest.

Cheat Sheet:

• Use SUM(amount) OVER (...) for cumulative totals.
• Partition by the entity you are tracking, such as account or customer.
• Order by business time plus a unique tie-breaker.
• Prefer ROWS for true row-by-row accumulation.
• Add opening balance separately.
• Expect sorting to dominate performance on big tables.

Practice Tasks:

• Modify the query to calculate running balance for only one account_id.
• Change the code so the balance resets every month.
• Add a column that flags when the running balance drops below zero.