📈Running totals
Ordered frames, one pass
Running totals are cumulative aggregates over an ordered window, typically SUM(…) OVER (ORDER BY … ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Specify frames explicitly while learning; RANGE behaves differently from ROWS when ties exist.
Running revenue by day
SELECT day, daily_revenue,
SUM(daily_revenue) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM (
SELECT order_date::date AS day, SUM(total_usd) AS daily_revenue
FROM orders
GROUP BY 1
) d
ORDER BY day;| day | daily_revenue | running_total |
|---|---|---|
| 2026-04-01 | 12000 | 12000 |
| 2026-04-02 | 9000 | 21000 |
| 2026-04-03 | 15000 | 36000 |
Partitioned running total (per customer)
SELECT customer_id, order_id, total_usd,
SUM(total_usd) OVER (PARTITION BY customer_id ORDER BY order_id) AS cust_running
FROM orders
WHERE customer_id = 441;| customer_id | order_id | total_usd | cust_running |
|---|---|---|---|
| 441 | 9001 | 44 | 44 |
| 441 | 9002 | 120.5 | 164.5 |
Silent nondeterminism
- ROWS vs RANGE confusion when ordering keys repeat.
- Computing totals after a join that multiplies rows, running sum on duplicated facts.
- Large partitions without supporting sort order, window sorts spill.
Tie-break ORDER BY
- Add stable tie columns to ORDER BY; document inclusive/exclusive frame intent.
- Pre-aggregate to correct grain before the window.
- Index partition keys + order columns for interactive workloads.
🧪Batch vs interactive
Same SQL, different SLO.
Ledger-style recomputation jobs can materialize running totals offline; OLTP may only need narrow partitions with idempotent replays if totals must be exact after retries.
🖼️Frame Mechanics: ROWS vs RANGE
Logical bounds vs physical counts.
The 'Ties' Trap
ROWS counts physical records (1 preceding = exactly 1 row back). RANGE counts logical values (1 preceding = all rows with the same value). If you have 10 rows with the same order_date, RANGE includes all 10 in the sum, while ROWS includes only one. Always use ROWS for deterministic running totals.
| Frame Type | Mechanical Behavior | Performance Cost |
|---|---|---|
| ROWS | Fixed offset in the sort buffer. | Fast. Simple pointer arithmetic. |
| RANGE | Scan for value boundaries. | Slower. Requires peek-ahead to find ties. |
| GROUPS | Treats blocks of ties as single units. | Advanced SQL:2011 feature; high CPU overhead. |
🎚️The Window Buffer
How the engine materializes the partition.
To compute a window function, the engine must sort the data by the PARTITION BY and ORDER BY keys. It then keeps a frame buffer in memory. If your partition is huge (e.g., millions of rows for one user), that buffer spills to disk and slows the query by orders of magnitude.
Performance: UNBOUNDED
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the most common frame; engines often optimize SUM and COUNT incrementally, but custom UDFs or exotic frames can devolve toward quadratic work, profile before assuming linear cost.
TL;DR
Running totals are cumulative sums over an ordered partition, implemented with window functions like SUM(x) OVER (ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The frame clause matters: ROWS counts physical neighbors; RANGE groups ties differently. Ensure a deterministic order key; ties make running results ambiguous without tie-break columns.