How do you return each transaction with both a month-to-date and a lifetime running balance?

## The requirement Statements and ledgers routinely want more than one accumulation on the same line: the balance since the start of the month, and the balance since the account opened, next to each transaction. Both are running totals of the same column over the same rows — they differ only in *where the accumulation restarts*. That is exactly what `PARTITION BY` controls, so the solution is two window specifications rather than two queries. ## Two windows in one SELECT ```sql SELECT account_id, txn_ts, amount, SUM(amount) OVER (PARTITION BY account_id, EXTRACT(YEAR FROM txn_ts), EXTRACT(MONTH FROM txn_ts) ORDER BY txn_ts, txn_id) AS mtd_balance, SUM(amount) OVER (PARTITION BY account_id ORDER BY txn_ts, txn_id) AS lifetime_balance FROM account_txn; ``` Window specifications are independent of one another. Each one partitions, orders and frames the same input rows in its own way, and each produces one value per row. There is no limit of one window per query, no requirement that the specifications agree, and no collapsing of rows: the result has exactly the rows of `account_txn`. Both windows rely on the default frame that an `ORDER BY` implies — from the start of the partition through the current row — which is precisely the cumulative semantics a balance needs. If you prefer to be explicit, or you want strict row-by-row accumulation regardless of ties, add `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. ## Building the month key The month partition must identify a month *in a year*. Partitioning by `EXTRACT(MONTH FROM txn_ts)` alone silently merges the same month across every year in the table — a bug that only appears once the data spans a year boundary, often in production. `EXTRACT(YEAR FROM ...)` and `EXTRACT(MONTH FROM ...)` are standard and widely available; engines also offer date-truncation helpers, but their names and behaviour differ, so a portable query either uses `EXTRACT` or stores a `month_key` column on the table. If the table already carries a `period` or `accounting_month` column, partition on that — it is cheaper to read and it matches whatever the business calls a month, which is not always the calendar one. ## Determinism A running balance is only well defined if the row order is. Two effects bite: - **Ties under the default frame.** The implied frame is `RANGE`-based, so rows tied on the window `ORDER BY` expression are peers and every one of them shows the same cumulative value — the total through the last tied row. Two transactions stamped at the same second therefore both display the post-both balance, and the ledger appears to jump. - **Reproducibility.** With ties present, which row prints first can vary between runs. Adding a unique tiebreaker — `ORDER BY txn_ts, txn_id` — removes both problems: the ordering becomes total, each row gets its own step, and the same query returns the same statement every time. ## Reading the result For one account with +100 on 31 Jan, +50 on 1 Feb and +25 on 2 Feb, `lifetime_balance` reads 100, 150, 175 while `mtd_balance` reads 100, 50, 75. The month-to-date column drops back at the month boundary because the partition changed; the lifetime column does not, because its partition did not. ## Variants of the same technique The pattern generalises to any set of "since when" measures on one line: year-to-date (partition on the year), quarter-to-date, a per-statement-cycle balance (partition on a cycle id), or a rolling 30-row exposure (same partition, an explicit `ROWS BETWEEN 29 PRECEDING AND CURRENT ROW` frame). You can mix cumulative and fixed-width frames in the same `SELECT`; they do not interfere. ## Common mistakes - Partitioning by month without the year, so January of two years accumulates together. - Believing a query may contain only one window specification, and running two queries plus a join instead. - Omitting `account_id` from one of the partitions, so one account's balance carries into the next. - Ordering only by a non-unique timestamp and being surprised that tied rows share a balance. - Filtering the query to one month and then claiming the lifetime column is still a lifetime balance — it can only accumulate over rows the query actually reads.