Window Functions — Looking Sideways Without Folding

In a nutshell

Window functions let you refer to other rows around a row without folding the rows. They solve questions that are hard with aggregation alone, such as ranking, running totals, and comparison with the previous value, in a single scan. In exchange, you must use them knowing exactly what the window (partition) and the frame are, because there are places where the defaults differ from intuition.

Why this was needed

To get "the 3 most expensive products per category" with GROUP BY alone, you need a self-join or a correlated subquery. It works by counting, for each product, "how many products in the same category are more expensive than me", but the code gets long and reads the table several times. "Month-over-month revenue change" is similar. You have to join the monthly aggregate with itself offset by one month, and handle the first month and empty months separately.

What the two questions have in common is that you have to look at the neighboring row while keeping each row. Aggregation folds rows, so it cannot do this. If each row can know which number it is within its own group and what the value of the row right before it is, the problem becomes simple. Window functions fill that place.

How it works

A window function has the form func() OVER (PARTITION BY ... ORDER BY ... frame).

PARTITION BY is the criterion for splitting windows. It is similar to GROUP BY but does not fold rows. If omitted, the whole result is a single window.
ORDER BY is the order within the window. It is the basis for ranking and running totals.
The frame decides how far within the window to look when computing the current row. Functions that look at several rows, such as sum, avg, and last_value, are affected by it.

You also need to know the evaluation timing. Window functions are computed after WHERE, GROUP BY, and HAVING are all finished. Two things follow from this. First, the result of a window function cannot be used in the WHERE of the same query. To filter by rank, wrap it one layer in a subquery or CTE and filter on the outside. Second, since aggregation is finished first, you can put an aggregate in the argument of a window function. sum(sum(total_amount)) OVER () first creates the per-group totals and then attaches the overall sum of those totals to each row. This is why you do not have to run the query twice when you compute a share.

SELECT category, id, price
FROM (
  SELECT category, id, price,
         row_number() OVER (PARTITION BY category ORDER BY price DESC, id) AS rn
  FROM products
) t
WHERE rn <= 3;

The commonly used functions divide as follows.

Function	What it does	Ties and boundaries
`row_number()`	Sequence number from 1	Ties get different numbers; who comes first is decided by the sort key
`rank()`	Rank	Ties share a rank, the next is skipped (1,1,3)
`dense_rank()`	Rank	Ties share a rank, the next is consecutive (1,1,2)
`sum() OVER (ORDER BY ...)`	Running total	The default frame includes tied rows too
`lag()` / `lead()`	Value of the previous / next row	A default if none, NULL if omitted
`ntile(n)`	Bucket number from 1 to n	Divides as evenly as possible, with sizes differing by at most 1

The thing to be most careful about is the default frame. If there is an ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and this means not "from the start of the partition to the current row" but "from the start up to the last row whose sort value equals the current row's". If there is no ORDER BY, all rows are tied with one another, so the frame is the whole partition.

What goes wrong in the field

The running total jumps like stairs. You computed cumulative revenue by order time, but two orders at the same time have the same cumulative value. This is because the default frame includes tied rows all at once. To accumulate one row at a time, specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly and add a tie-break column such as id to the sort key.

last_value returns the current row. You wanted the last value of the partition, but the default frame ends at the current row, so you get the value of the current row (or its ties). The PostgreSQL documentation also warns that this combination easily produces useless results. Widen the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or reverse the sort and use first_value.

The first order changes from run to run. You picked each customer's first order with row_number() ... ORDER BY ordered_at, but if two orders are at the same time, it is not determined which becomes number 1. Today it is right and tomorrow a different answer comes out. Add columns until the sort key becomes unique.

lag() skips empty months. A month with no revenue has no row at all in the aggregated result, so March's lag() returns January's value. The result says "month over month". If you need to compare including empty months, first build a calendar with generate_series and LEFT JOIN.

WHERE changes the denominator. Window functions are computed after WHERE, so if you keep only a certain status with WHERE and compute a share, it is a "share among the remaining rows". If you need a share relative to the whole, compute it before filtering and filter on the outside.

Rounded shares do not add up to 100. If you round each channel's share to two decimal places, the sum can be 99.99 or 100.01. The calculation is not wrong; it is a rounding error. If you include the total in a report, write down why that difference arises.

How to check

Before trusting a result, verify the invariants with queries.

-- every partition has exactly one row numbered 1
SELECT count(*) FILTER (WHERE rn = 1) = count(DISTINCT category) FROM v_ranked_products;

-- the last running total equals the plain total
SELECT max(cum_revenue) = sum(revenue) FROM v_running_revenue;

-- the first month has no previous value, the others do
SELECT count(*) FILTER (WHERE prev_revenue IS NULL) FROM v_mom;

-- bucket sizes differ by at most one
SELECT quartile, count(*) FROM v_price_quartile GROUP BY quartile ORDER BY 1;

You can also check with the execution plan. If you add EXPLAIN, the window function appears as a WindowAgg node, and usually beneath it there is a Sort on the PARTITION BY and ORDER BY keys. You can also see that if you use several OVER clauses with different sort keys, Sort and WindowAgg stack up layer upon layer accordingly. If a window query is slow on a large table, look at this sort first.

What you will do in the next lab

First, in the aggregation lab of the previous reading, you build GROUP BY, HAVING, FILTER, and monthly aggregation, and then in the window function lab you implement, in turn, sequence numbers by category, a comparison of two kinds of rank, cumulative revenue, month over month, share by channel, the top 3 per group, price quartiles, and each customer's first order. If you run the check queries above as they are on the views you built in the lab, you can verify your own answers before grading.