Running total with a window function
Compute cumulative sums per group without self-joins using SUM() OVER (PARTITION BY ... ORDER BY ...).
- SQL
- GreenSnippets
- MIT
- Executed with PostgreSQL 17
SELECT customer_id,
created_at,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY created_at, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders
ORDER BY customer_id, created_at, id;How it works
A window function computes a value for each row using related rows, without collapsing them like GROUP BY. PARTITION BY restarts the total per customer, and ORDER BY defines the running order.
Add a unique tie-breaker (id) to the ORDER BY. With the default frame, rows that tie on the order key receive the same running total, which usually surprises people. The explicit ROWS frame below gives one step per row.
Related snippets
- Python Tested
Time a block of code with a context manager
Measure elapsed wall-clock time of a with-block using time.perf_counter.
- PHP Tested
Read a large CSV file with a generator
Stream CSV rows as associative arrays using fgetcsv, without loading the whole file into memory.
- JavaScript Tested
Run async tasks with a concurrency limit
Process a large list with at most N promises in flight at once, preserving result order.
- Bash
Run commands in parallel with xargs
Process many files concurrently with xargs -P, safely handling spaces and newlines in names.