MediumWindow Functions

Cumulative Running Cashflow & Ledger Balance

Frequently Asked By:StripeCoinbaseBloomberg

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

Given a double-entry bank transactions ledger with transaction_id, account_id, transaction_timestamp, and amount, calculate the real-time running balance after each transaction for every account.

Why Technical Interviewers Ask This

Financial platforms like Stripe and Coinbase must compute chronological ledger balances on the fly. Candidates must demonstrate proper ordering and explicit frame specification (ROWS BETWEEN vs RANGE BETWEEN).

Common Pitfalls & Interview Traps

Relying on default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which bundles duplicate timestamps together into the same running total instead of stepping row-by-row.

Omitting PARTITION BY account_id, resulting in running balances leaking across unrelated user accounts.

Inefficient self-joins with T1.id <= T2.id causing O(N^2) quadratic performance on large ledgers.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

SELECT
  transaction_id,
  account_id,
  transaction_timestamp,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY transaction_timestamp ASC, transaction_id ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_balance
FROM ledger_transactions
ORDER BY account_id, transaction_timestamp ASC;

Approach Breakdown

Using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly instructs the SQL execution engine to accumulate physical rows up to the current entry. Adding transaction_id ASC as a secondary tie-breaker ensures deterministic balance progression even if two events share identical microsecond timestamps.

Edge Cases to Verbally Clarify with Your Interviewer

Two transactions occur at the exact same millisecond timestamp (resolved via secondary unique key order).

Negative amounts representing debits/withdrawals (summed algebraically).

Accounts with only 1 transaction.

Frequently Asked Follow-Up Questions

Why use ROWS instead of RANGE in running total window functions?

By default, ORDER BY inside an OVER() clause uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. If two rows share the same ORDER BY value, RANGE sums both rows together simultaneously. ROWS processes physical rows strictly one-by-one, maintaining true chronological progression.