Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 3 · Lesson 17 · 6 min read

PostgreSQL Window Functions Explained

On this page
  1. What window functions do
  2. The OVER clause
  3. Recipe: row_number, rank and top N per group
  4. Recipe: compare with the previous row using lag and lead
  5. Recipe: running totals and moving averages
  6. Recipe: split rows into buckets with ntile
  7. Check yourself

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:

SQL
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:

SQL
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.00

Empty 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:

SQL
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 | 2

row_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:

SQL
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.00

Adding 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":

SQL
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.00

The 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:

SQL
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:

SQL
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:

SQL
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:

SQL
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.