Postgres Window Functions: Running Totals, Rankings and Top-N per Group
Window functions compute values across related rows without collapsing them like GROUP BY. OVER, PARTITION BY and ORDER BY explained, with practical queries: ranking, top-N per group, running totals, moving averages, comparing with the previous row (LAG), and frames.
GROUP BY collapses rows: one row per customer, with a total. Window functions calculate across related rows but keep every row — so you can show each order and the customer's total, or each order's rank among that customer's orders. (SQL GROUP BY)
The shape
function(...) OVER (
PARTITION BY ... -- which rows belong together (like GROUP BY, without collapsing)
ORDER BY ... -- order within each partition
frame ... -- which rows around the current one to include (optional)
)
The examples use an orders table with id, customer_id, total and created_at.
Each row with its group's total
SELECT id, customer_id, total,
SUM(total) OVER (PARTITION BY customer_id) AS customer_total,
round(100.0 * total / SUM(total) OVER (PARTITION BY customer_id), 1) AS pct_of_customer
FROM orders;
Every order row stays, with the customer's total alongside.
Ranking
SELECT customer_id, id, total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn,
RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS dense
FROM orders;
| Function | Ties |
|---|---|
ROW_NUMBER() |
Always 1, 2, 3… (ties broken arbitrarily — add a tie-breaker column) |
RANK() |
Ties share a rank, then skip: 1, 1, 3 |
DENSE_RANK() |
Ties share a rank, no gaps: 1, 1, 2 |
Top-N per group (the classic)
"Each customer's 3 most recent orders":
SELECT *
FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id DESC) AS rn
FROM orders o
) ranked
WHERE rn <= 3;
You can't filter on a window function in WHERE directly (it's computed after WHERE), hence the subquery — or a CTE. (Postgres CTEs)
For "latest one per group", Postgres also has DISTINCT ON:
SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, created_at DESC;
Running totals
SELECT date_trunc('day', created_at) AS day,
SUM(total) AS revenue,
SUM(SUM(total)) OVER (ORDER BY date_trunc('day', created_at)) AS cumulative
FROM orders
GROUP BY day
ORDER BY day;
The window runs over the grouped result. ORDER BY inside OVER makes the sum cumulative.
Moving averages: frames
SELECT day, revenue,
AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM daily_revenue;
The frame (ROWS BETWEEN …) picks which neighbouring rows to include — here the current day and the six before it. Watch out: with ORDER BY and no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with equal ordering values as peers — a source of surprises.
Comparing with the previous row: LAG and LEAD
SELECT day, revenue,
LAG(revenue) OVER (ORDER BY day) AS previous_day,
revenue - LAG(revenue) OVER (ORDER BY day) AS change
FROM daily_revenue;
LAG looks back, LEAD looks forward. Great for day-over-day changes, time between a user's sessions, or detecting gaps.
Also useful: FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE(4) (quartiles), PERCENT_RANK().
Naming windows
Reuse a window definition:
SELECT id,
SUM(total) OVER w AS running,
AVG(total) OVER w AS running_avg
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY created_at);
Performance
Window functions sort data by PARTITION BY + ORDER BY. An index on those columns (e.g. (customer_id, created_at)) can let Postgres avoid a big sort. Check with EXPLAIN ANALYZE. (Reading EXPLAIN ANALYZE, Database indexes)
EasySpawn gives your app its own Postgres database and a terminal with Claude Code, so "revenue by day with a 7-day average" is one question away. See how it works or join the waitlist.
Related: Postgres CTEs · SQL GROUP BY Explained · WHERE vs HAVING · Postgres Materialized Views
Keep reading
UUIDv7 Explained: Time-Ordered IDs and Why Postgres 18 Added uuidv7()
UUIDv7 is a UUID that starts with a millisecond timestamp, so new IDs sort in creation order. Why that makes database indexes faster than random UUIDv4, the format in detail, generating them in Postgres 18, Node.js and Python, what the timestamp leaks, and when to still use v4 or bigint.
SQLite WAL Mode: Faster Writes and Readers That Don't Block
Write-ahead logging (WAL) mode is the single most important setting for using SQLite in a web app: readers no longer block the writer, writes get faster, and "database is locked" errors mostly disappear. How to turn it on, the companion pragmas, checkpoints, the -wal and -shm files, safe backups, and its limits.