Running Balance per Account

Each account has a stream of signed transactions (deposits positive, withdrawals negative). Compute the running balance after each transaction, restarting per account.

Schema

CREATE TABLE transactions (
    account  TEXT,
    txn_date DATE,
    amount   NUMERIC
);

Task

For each row return account, txn_date, amount, and running_total — the cumulative sum of amount within the account up to and including that transaction, in date order. Order the output by account ascending, then txn_date ascending.

Expected output

Approach

A cumulative sum is SUM(amount) turned into a window function with an ORDER BY. Partition by account so the balance resets for each account, order by txn_date so earlier transactions accumulate into later ones, and set the frame to UNBOUNDED PRECEDING ... CURRENT ROW so each row sees all prior rows in its partition plus itself.

Query

SELECT
    account,
    txn_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY account
        ORDER BY txn_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM transactions
ORDER BY account, txn_date;

Walkthrough

• Account A, ordered by date: 100 → running 100; +(-40) → 60; +200 → 260; +(-60) → 200.
• Account B, ordered by date: 500 → running 500; +(-100) → 400.
• The PARTITION BY account clause means A’s final 200 never influences B, and vice versa — each partition starts fresh.
• The outer ORDER BY account, txn_date groups the rows for display; the running total itself is fixed by the window’s own ORDER BY.

Complexity & notes

• The default frame when you specify ORDER BY but omit a frame clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With unique dates per account that produces the same result, but if two transactions shared a date, RANGE would lump them into one cumulative step (they’d show the same total), whereas ROWS advances row by row. Spelling out ROWS avoids that surprise.
• A composite index on (account, txn_date) lets the engine feed rows to the window in partition/order without an extra sort.
• Cost is one sort per the partition/order key, roughly O(n log n).
• SQL Server and Oracle support the same SUM() OVER (... ROWS BETWEEN ...) syntax; MySQL 8+ does too. Pre-8 MySQL lacks window functions and needs a self-join or session-variable trick instead.