Every reporting query has a moment where GROUP BY stops being enough. You need the aggregate and the row it came from: each customer’s orders with a running total beside them, each player’s score with their rank, each month with the delta from the month before. The old toolbox answer was a self-join, a correlated subquery, or — worse — pulling the data into application code and looping. Window functions, a SQL standard feature that PostgreSQL has shipped since 8.4, make all of that a single scan with a declarative frame.
The paradox that trips people up is that window functions look like aggregates but refuse to collapse rows. SUM(amount) over a group returns one row per group; SUM(amount) OVER (...) returns one value per input row, computed across a window of rows that the function can see. Once that distinction clicks, the rest is syntax. This post covers the anatomy, the classic patterns, and the pitfalls that cost people an afternoon.
Anatomy: Partition, Order, Frame
Every window function call has up to three parts. PARTITION BY divides rows into independent groups — think of it as a GROUP BY that does not collapse. ORDER BY sorts rows within each partition, which matters for ranking and for anything frame-dependent. The frame specifies which surrounding rows the function sees; by default, with an ORDER BY present, it is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — everything up to and including the current row and its ties. The official window function tutorial walks through this in more depth.
-- Running revenue per customer, newest last:
SELECT customer_id,
created_at,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY created_at
) AS running_total
FROM orders
ORDER BY customer_id, created_at;The Ranking Trio: row_number, rank, dense_rank
Three functions, three subtly different answers to “what place did this row take?” row_number() assigns unique sequential numbers, ties broken arbitrarily (or deterministically with a tiebreaker in ORDER BY). rank() gives ties the same rank and skips subsequent positions — 1, 1, 3. dense_rank() gives ties the same rank without gaps — 1, 1, 2. The classic applications: deduplicating a table by keeping row_number() = 1 per key, and leaderboards where ties must share a medal.
-- Leaderboard with ties handled three ways:
SELECT player,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
RANK() OVER (ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense
FROM game_scores;
-- Keep the newest row per customer (dedup pattern):
DELETE FROM orders_staging o
USING (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC
) AS rn
FROM orders_staging
) ranked
WHERE o.id = ranked.id
AND ranked.rn > 1;lag and lead: Comparing a Row to Its Neighbor
lag() and lead() peek at the previous or next row in the partition, which is the entire secret behind month-over-month reporting. Both take an optional offset and a default for the boundary rows where no neighbor exists — without the default, boundaries come back NULL and your delta column is full of nulls.
-- Month-over-month revenue change:
WITH monthly AS (
SELECT date_trunc('month', created_at) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT month,
revenue,
lag(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue,
revenue - lag(revenue, 1, 0) OVER (ORDER BY month) AS mom_delta
FROM monthly
ORDER BY month;Frames: Running Totals, Moving Averages, Percent of Total
Frames give you moving windows: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is a trailing seven-row average; ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING sees the whole partition, which is how you compute a percent-of-total without a second scan. The keyword you must be explicit about is ROWS versus RANGE — the default RANGE includes ties, so a running total over timestamps with duplicates will l ump duplicates together instead of advancing one row at a time.
-- Trailing 7-day average (rows, not range — explicit beats implicit):
SELECT day,
revenue,
AVG(revenue) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS avg_7d
FROM daily_revenue
ORDER BY day;
-- Percent of total per partition:
SELECT customer_id,
amount,
ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY customer_id), 1)
AS pct_of_customer_total
FROM orders;Worked Example: Customer Revenue Analysis
Putting it together — a quarterly report that ranks customers by revenue, flags each order above the customer’s own average, and shows cumulative revenue, all in one pass:
WITH order_metrics AS (
SELECT o.customer_id,
o.id AS order_id,
o.amount,
AVG(o.amount) OVER (PARTITION BY o.customer_id) AS customer_avg,
RANK() OVER (PARTITION BY o.customer_id
ORDER BY o.amount DESC) AS amount_rank,
SUM(o.amount) OVER (PARTITION BY o.customer_id
ORDER BY o.created_at
ROWS UNBOUNDED PRECEDING) AS cumulative
FROM orders o
WHERE o.created_at >= date_trunc('quarter', CURRENT_DATE)
)
SELECT customer_id,
order_id,
amount,
customer_avg,
amount_rank,
cumulative,
amount > customer_avg AS above_avg
FROM order_metrics
ORDER BY customer_id, amount_rank;Banding and Peers: ntile, first_value, nth_value
Two less-celebrated functions round out the toolbox. NTILE(4) splits each partition into four roughly equal bands — the instant answer to “quartile each customer by spend” without a CASE ladder over computed thresholds. FIRST_VALUE() and NTH_VALUE() pull a value from a specific row in the frame, which is how you attach “first purchase amount” or “highest-scoring attempt” to every row of a partition. Both respect the same frame rules as everything else, so the tie-handling caveats apply here too: if two rows tie at the boundary, what counts as “first” depends on your ORDER BY, and adding a deterministic tiebreaker column to it is cheap insurance.
-- Quartile customers by lifetime spend, show first order alongside:
SELECT customer_id,
lifetime_spend,
NTILE(4) OVER (ORDER BY lifetime_spend DESC) AS spend_quartile,
FIRST_VALUE(order_amount) OVER (
PARTITION BY customer_id
ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS first_order_amount
FROM customer_summary;Pitfalls That Cost an Afternoon
- Window functions are not allowed in WHERE. They are evaluated after grouping, so filtering on them requires a subquery or CTE — filter the CTE’s output, not the window expression itself.
- The default frame includes ties. With
ORDER BYpresent, the implicit frame isRANGE ... CURRENT ROW, which includes peer rows. When duplicates in your sort key make running totals jump in blocks, you wantedROWS. - Forgetting PARTITION BY. Without it, the partition is the whole table — your “per customer” running total silently becomes a grand total. The query runs fine; the numbers are wrong.
- NULL ordering surprises. In ascending sorts, NULLs sort last by default; in descending sorts, first. If NULLs represent missing data rather than infinity, put
NULLS LASTin the window’s ORDER BY explicitly.
The habit worth building is simple: whenever you are about to join a table to its own aggregate, or loop over rows in application code to compute something cumulative, check whether a window function says it in one expression. Nine times out of ten it does — and the reference page for window functions is shorter than the self-join you were about to write.