PostgreSQL Window Functions Explained
On this page
What window functions do
GROUP BY answers "total per customer", but it collapses each customer into one row. Often you want to keep every row and see something about its neighbors. What share of the total was this order? Is it bigger than the customer's last one? What's the running total so far?
Window functions do exactly that. They compute across a set of related rows, called the window, and still return one row per input row. Here's a small table to try them on:
CREATE TABLE orders (
order_id int PRIMARY KEY,
customer text NOT NULL,
order_date date NOT NULL,
amount numeric(10,2) NOT NULL
);
INSERT INTO orders VALUES
(1, 'Ana', '2026-01-05', 120), (2, 'Ana', '2026-02-11', 80),
(3, 'Ana', '2026-03-02', 120), (4, 'Ben', '2026-01-20', 300),
(5, 'Ben', '2026-02-14', 50), (6, 'Cy', '2026-03-30', 200);The OVER clause
Any aggregate becomes a window function when you add OVER. The rows stay, and each one gets the result:
SELECT order_id, customer, amount,
sum(amount) OVER () AS grand_total,
sum(amount) OVER (PARTITION BY customer) AS customer_total
FROM orders;
-- order 1: Ana 120.00 | 870.00 | 320.00Empty OVER () makes the whole result one window. PARTITION BY customer splits it into one window per customer, like GROUP BY without collapsing the rows. The rest of this lesson is recipes built from those two ideas plus ORDER BY.
Recipe: row_number, rank and top N per group
Add ORDER BY inside OVER and you can number rows within each partition. There are three ranking functions, and they differ only in how they treat ties:
SELECT customer, order_id, amount,
row_number() OVER w AS row_number,
rank() OVER w AS rank,
dense_rank() OVER w AS dense_rank
FROM orders
WHERE customer = 'Ana'
WINDOW w AS (PARTITION BY customer ORDER BY amount DESC);
-- order 1 | 120 | 1 | 1 | 1
-- order 3 | 120 | 2 | 1 | 1
-- order 2 | 80 | 3 | 3 | 2row_number never ties. rank gives ties the same number and then skips ahead (1, 1, 3). dense_rank doesn't skip (1, 1, 2). The WINDOW clause names a window once so several functions can share it.
The most common use is "top N per group", such as each customer's biggest order. You can't filter on a window function in WHERE, because WHERE runs before window functions are computed. Wrap the query and filter outside:
SELECT customer, order_id, amount
FROM (
SELECT *, row_number() OVER (PARTITION BY customer ORDER BY amount DESC, order_id) AS rn
FROM orders
) ranked
WHERE rn = 1;
-- Ana 1 120.00 | Ben 4 300.00 | Cy 6 200.00Adding order_id to the ORDER BY breaks ties the same way every time. Without it, which of Ana's two 120 orders wins could change from run to run.
Recipe: compare with the previous row using lag and lead
lag looks back a number of rows (one by default), and lead looks forward. They're the standard tool for "change since last time":
SELECT customer, order_date, amount,
lag(amount) OVER (PARTITION BY customer ORDER BY order_date) AS previous,
amount - lag(amount) OVER (PARTITION BY customer ORDER BY order_date) AS change
FROM orders
WHERE customer = 'Ana';
-- 2026-01-05 | 120.00 | NULL | NULL
-- 2026-02-11 | 80.00 | 120.00 | -40.00
-- 2026-03-02 | 120.00 | 80.00 | 40.00The first row has no previous row, so you get NULL. Pass a default as the third argument, lag(amount, 1, 0), to get 0 instead. lag(revenue, 12) on monthly data compares each month with the same month a year earlier.
Recipe: running totals and moving averages
Here's the rule that confuses everyone. When you add ORDER BY inside OVER, an aggregate stops covering the whole partition. It now covers only the rows up to the current one. That turns sum into a running total:
SELECT order_date, amount,
sum(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
-- 2026-01-05 | 120.00 | 120.00
-- 2026-01-20 | 300.00 | 420.00
-- 2026-02-11 | 80.00 | 500.00 ...The set of rows an aggregate sees is called the frame, and you can set it yourself. A moving average over the current row and the two before it:
SELECT order_date, amount,
round(avg(amount) OVER (ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM orders;ROWS counts physical rows. The default frame uses RANGE instead, which treats rows with the same ORDER BY value as one group. So if two orders share a date, a running total with the default frame includes both on both rows. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW if you want it to count strictly row by row.
The frame also explains a classic surprise with last_value. With ORDER BY and the default frame, the "last" row it can see is the current row, so it just returns the current value. Give it the whole partition:
SELECT customer, order_date,
last_value(amount) OVER (PARTITION BY customer ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_amount
FROM orders;Recipe: split rows into buckets with ntile
ntile(n) deals rows into n roughly equal buckets in order. Quartiles, deciles and "top 10% of customers" all come from it:
SELECT customer, sum(amount) AS spent,
ntile(4) OVER (ORDER BY sum(amount) DESC) AS quartile
FROM orders
GROUP BY customer;Notice that this combines GROUP BY and a window function. Window functions run after grouping, so they can use aggregates like sum(amount).
Check yourself
row_number() OVER (PARTITION BY product ORDER BY quantity DESC) in a subquery, then keep rn = 1 in the outer query. Add a tie-breaker to the ORDER BY so the result is stable.
ORDER BY inside OVER turns the sum into a running total. For the full total on every row, drop the ORDER BY, or set the frame to cover the whole partition.
Window functions are computed after WHERE runs, so WHERE can't see them. Compute the rank in a subquery or CTE and filter in the outer query.
For scores 100, 90, 90, 80: row_number() gives 1, 2, 3, 4 and breaks the tie arbitrarily, rank() gives 1, 2, 2, 4 and skips a number, and dense_rank() gives 1, 2, 2, 3 with no gap. Pick based on whether ties should share a position and whether gaps matter.
Use lag(total) OVER (ORDER BY day), which returns the value from the previous row in that ordering. Subtract it from total to get the daily change. The first row has no previous value, so lag returns NULL there unless you give it a default.