Window Functions

A window function computes over a set of rows related to the current row and returns a value for every one of them. sum(total) under a GROUP BY gives one row per customer. The same sum(total) written OVER (PARTITION BY customer_id) gives back every order, each one carrying its customer's total alongside it. Nothing collapses, and nothing has to be joined back on afterwards.

That difference removes more application loops than anything else in this chapter. Cartwheel's "your recent orders" screen currently costs three round trips, one of which exists purely to fetch a customer's entire order history so the service can add up a running total in Python. One OVER clause deletes that query, and the same feature deletes the loop that computes the gap between two delivery events.

OVER, and What It Changes

A window is a set of rows the function is allowed to look at while it computes a value for the current row. PARTITION BY cuts the result into independent groups; ORDER BY inside the OVER clause fixes the order within each group, without which "running", "previous" and "rank" mean nothing. The row count of the query does not change. Window functions are evaluated after WHERE, GROUP BY and HAVING have already run, so they see exactly the rows that survived those clauses and no others.

SELECT customer_id, count(*), sum(total)
  FROM orders
 WHERE placed_at >= '2026-08-01'
 GROUP BY customer_id;                    -- one row per customer

SELECT id, customer_id, placed_at, total,
       sum(total) OVER (PARTITION BY customer_id
                        ORDER BY placed_at) AS running_spend
  FROM orders
 WHERE placed_at >= '2026-08-01';         -- every order, plus the total

The first query answers "how much has each customer spent" and throws the orders away. Written as a self-join, the second would read orders twice and correlate the table with itself; written as a window it makes one pass. What it answers is "how much had this customer spent as of this order", and it keeps the rows — a list of orders with a number beside each one, which is the shape the screen actually needs.

The Three Families

Ranking functions number the rows: row_number() assigns 1, 2, 3 and never ties, rank() gives tied rows the same number and then skips (1, 1, 3), dense_rank() gives tied rows the same number and does not skip (1, 1, 2), and ntile(n) splits the partition into n roughly equal buckets. Offset functions read a different row of the same partition: lag() and lead() step backwards and forwards, first_value(), last_value() and nth_value() pick a row out of the frame. And any ordinary aggregate — sum, count, avg, max — becomes a window function the moment you attach OVER to it.

SELECT order_id, event_type, occurred_at,
       occurred_at - lag(occurred_at) OVER (PARTITION BY order_id
                                            ORDER BY occurred_at) AS since_prev
  FROM delivery_events
 WHERE occurred_at >= date_trunc('day', now());

lag() returns the value of occurred_at from the previous row of the same order, so subtracting it produces the interval between one event and the one before it, on the row where it belongs. The first event of each order gets NULL, because there is no previous row — and lag() takes an optional default argument for exactly that case. The database does it inside the scan it was already doing, where Cartwheel currently pulls a day of delivery_events — about 4 million rows — into the analytics job and diffs them in Python.

Frames, and the Default That Surprises People

Inside a partition, the function does not necessarily see the whole partition. It sees a frame, and the default frame is where correct-looking running totals go wrong. When the window has an ORDER BY, the default is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: the frame runs from the start of the partition through the current row's last peer, where a peer is any row the window's ORDER BY sorts as equivalent. Without an ORDER BY every row is a peer of every other, so the frame is the entire partition — which is why sum(total) OVER (PARTITION BY customer_id) gives the same grand total on every row.

sum(total) OVER (PARTITION BY customer_id ORDER BY placed_at)
-- RANGE (the default): every order sharing this exact placed_at
-- is already counted, on all of those rows

sum(total) OVER (PARTITION BY customer_id ORDER BY placed_at
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- ROWS: strictly this row and the rows physically before it

In RANGE mode the running total jumps by the whole tie group at once and then repeats that value across every tied row. In ROWS mode it advances one row at a time, and that is almost certainly what the query was written to do. The bug never shows up in a test fixture with distinct timestamps and always shows up in production, and it becomes certain rather than likely the moment the ordering key is coarse — order by date_trunc('day', placed_at) and every order placed that day is a peer of every other. A third mode, GROUPS, counts offsets in peer groups rather than rows, and a frame_exclusion clause (EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES) removes the current row or its peers from a frame that would otherwise include them.

The same default explains a second piece of folklore. last_value() over an ordered window returns the current row, every time, and people conclude the function is broken. It is not: the frame ends at the current row's last peer, so the last value in the frame is the current row. The fix is to say what you meant — ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — or to use first_value() over the reversed ordering instead.

FILTER and Named Windows

Aggregates used as window functions accept a FILTER clause, which feeds only the matching rows to the function and discards the rest. It expresses "count the cancelled ones" without the CASE WHEN … THEN 1 END contortion, and it is restricted to aggregates — ranking and offset functions do not take it. A WINDOW clause names a definition so several functions can share it, which is a performance decision as much as a stylistic one.

SELECT c.city, o.id, o.total,
       rank()       OVER w                              AS rank_in_city,
       sum(o.total) OVER w                              AS city_running_total,
       count(*) FILTER (WHERE o.status = 'cancelled')
                    OVER w                              AS cancelled_so_far
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
 WINDOW w AS (PARTITION BY c.city ORDER BY o.placed_at);

All three functions reference the same named window, so Postgres computes them together over one ordering of the data. OVER w and OVER (w …) are not the same thing: the bare form uses the named definition, while the parenthesized form copies it and modifies it. Postgres rejects the second outright if the referenced definition already contains a frame clause.

What a Window Costs

A window function is executed by a WindowAgg node, and that node requires its input already sorted by the partition keys followed by the ordering keys. The planner has two ways to supply that: sort the rows, or read them in order from an index whose columns match. On orders, a B-tree on (customer_id, placed_at) turns the sort into an ordered scan and the Sort node disappears from the plan entirely — the same trick Chapter 8 uses for ORDER BY … LIMIT, applied to a different consumer.

Several functions sharing one window definition are evaluated in a single WindowAgg over a single ordering. Several different definitions each get their own node, and each node that needs a different order costs another sort of the intermediate result. Over 40 million orders that is the difference between a query that runs on the primary and one that runs on pg-replica-a at night. Five windows that differ only in whether they say ORDER BY placed_at or ORDER BY placed_at DESC are five sorts of the same rows, and Chapter 9 makes reading that off a plan routine.

Where Windows Beat the Alternatives

The recurring shapes are top-N per group, deduplication, gaps and islands, and period-over-period change. Each one traditionally costs either a self-join with a correlated subquery or a loop in the application, and each becomes one pass over sorted input. There is one structural restriction to plan around: window functions are permitted only in the select list and the ORDER BY clause, because WHERE is evaluated before the windows are computed. A rank has to be produced in a subquery or a CTE and filtered from outside it.

SELECT id, order_id, event_type, occurred_at
  FROM (SELECT de.*,
               row_number() OVER (PARTITION BY order_id, event_type
                                  ORDER BY occurred_at DESC) AS rn
          FROM delivery_events de
         WHERE occurred_at >= '2026-08-12') ranked
 WHERE rn = 1;

The inner query numbers each order's events of a given type, newest first; the outer query keeps the number-one row of each group. Writing WHERE row_number() OVER (…) = 1 directly is rejected, because WHERE runs before the windows are computed. The subquery is that evaluation order made visible. For this particular shape, one row per group, a later topic in this chapter has a shorter and usually faster Postgres-specific form; the window version earns its place when the rank itself is part of the answer, or when you need the second and third rows too.