Window Functions and CTEs

The OVER clause

A window function sees a set of rows and still returns one value per row. Nothing is collapsed, which is the whole difference from GROUP BY.

SELECT region, day, amt,
       sum(amt)     OVER (PARTITION BY region)                AS region_total,
       row_number() OVER (PARTITION BY region ORDER BY amt DESC) AS rn,
       amt / sum(amt) OVER (PARTITION BY region)              AS share
FROM sales;

Three parts, all optional. PARTITION BY splits the rows into groups, ORDER BY orders them inside a partition, and the frame clause limits which rows in that ordering the function sees.

- OVER () is one partition of every row, in no defined order. - Window functions run after WHERE, GROUP BY, and HAVING, so you cannot filter on one in WHERE. Wrap the query in a subquery or CTE and filter outside. - You can use an aggregate as a window function, and you can combine GROUP BY with a window that then operates on the grouped rows.

The function table

lag and lead return NULL at the edges unless you pass a default.

-- Day-over-day delta and percent change
SELECT day, amt,
       amt - lag(amt) OVER (ORDER BY day)                          AS delta,
       round(100.0 * (amt - lag(amt) OVER (ORDER BY day))
             / nullif(lag(amt) OVER (ORDER BY day), 0), 1)         AS pct
FROM sales ORDER BY day;

Frames and the default-frame trap

The frame clause is {ROWS | RANGE | GROUPS} BETWEEN start AND end, with bounds UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING.

The default frame depends on whether you wrote ORDER BY, and this is the single most common window-function bug.

So last_value(amt) OVER (ORDER BY day) returns the current row's own amt, not the partition's final value. Spell the frame out:

SELECT day, amt,
       last_value(amt) OVER (ORDER BY day)                  AS wrong,   -- current row
       last_value(amt) OVER (ORDER BY day
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS right_
FROM sales;

Note also that the default is RANGE, so with ORDER BY day every row sharing a date is inside the frame, and a "running total" over duplicated dates jumps by the whole day's worth at once. Use ROWS when you mean rows.

-- Running total, 7-row moving average, and a value-based window
SELECT day,
  sum(amt) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING)              AS running,
  avg(amt) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7,
  sum(amt) OVER (ORDER BY day
                 RANGE BETWEEN '7 days' PRECEDING AND CURRENT ROW)   AS last_7d
FROM sales;

ROWS UNBOUNDED PRECEDING is shorthand for BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, and EXCLUDE NO OTHERS (11+) trim the frame further, which is how you get "average of the others".

Named windows and top-N per group

Repeating a long OVER (...) is what the WINDOW clause is for. It sits after HAVING and before ORDER BY.

SELECT region, day, amt,
       row_number() OVER w AS rn,
       rank()       OVER w AS rk,
       sum(amt)     OVER (w ROWS UNBOUNDED PRECEDING) AS running
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY day)
ORDER BY region, day;

Top-N per group needs the filter outside the window, because WHERE runs first.

-- Top 3 orders per customer
SELECT * FROM (
  SELECT o.*, row_number() OVER (PARTITION BY customer_id
                                 ORDER BY total DESC, id) AS rn
  FROM orders o
) t
WHERE rn <= 3;

For exactly one row per group, DISTINCT ON is shorter and usually faster, since it can stop at the first row of each group given the right index.

SELECT DISTINCT ON (customer_id) customer_id, id, total
FROM orders
ORDER BY customer_id, total DESC, id;

Use rank() instead of row_number() when ties should all survive the rn <= 3 cut.

WITH: common table expressions

WITH names a subquery so the main query can read it, and reuse it, by name.

WITH paid AS (
  SELECT * FROM orders WHERE status = 'paid'
), per_customer AS (
  SELECT customer_id, count(*) AS n, sum(total) AS revenue
  FROM paid GROUP BY customer_id
)
SELECT c.email, p.n, p.revenue
FROM per_customer p JOIN customers c ON c.id = p.customer_id
WHERE p.revenue > 1000
ORDER BY p.revenue DESC;

Before 12 a CTE was always an optimization fence, materialized once and never inlined. From 12 the default is: inline it if it is referenced exactly once, is not recursive, and has no side effects. Override either way.

Reach for MATERIALIZED when a CTE is expensive and read several times, or when inlining makes the planner recompute a volatile function. Reach for NOT MATERIALIZED when a fence is stopping a WHERE from pushing into the CTE and you see a full scan in EXPLAIN.

WITH RECURSIVE

Two branches joined by UNION ALL: a non-recursive seed, then a term that references the CTE name. Iteration stops when the recursive term returns no rows. UNION instead of UNION ALL deduplicates, which can stop a cycle at the cost of a hash of every row.

-- Walk a tree down from the roots, carrying depth and a sort path
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth, ARRAY[id] AS path
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || c.id
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT id, repeat('  ', depth - 1) || name AS label, depth
FROM tree
ORDER BY path;

-- A generated series, and the ancestors of one node
WITH RECURSIVE fib(a, b) AS (
  SELECT 0, 1
  UNION ALL
  SELECT b, a + b FROM fib WHERE b < 1000
)
SELECT a FROM fib;

WITH RECURSIVE up AS (
  SELECT * FROM categories WHERE id = 42
  UNION ALL
  SELECT c.* FROM categories c JOIN up ON up.parent_id = c.id
)
SELECT * FROM up;

Dirty data with a loop in it will spin forever. Guard it with a depth cap, a path array check, or the CYCLE clause (14+):

WITH RECURSIVE tree AS (
  SELECT id, parent_id FROM categories WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id FROM categories c JOIN tree t ON c.parent_id = t.id
) CYCLE id SET is_cycle USING cycle_path
SELECT id, is_cycle FROM tree;

SEARCH BREADTH FIRST BY id SET ord and SEARCH DEPTH FIRST BY id SET ord (14+) generate the ordering column for you instead of hand-rolling a path array. The recursive term may reference the CTE only once, and not inside an outer join, a subquery, or an aggregate.

Data-modifying CTEs

INSERT, UPDATE, DELETE, and MERGE can all live in a WITH, as long as they carry RETURNING. Everything runs in one statement and one snapshot.

-- Archive and delete in one statement
WITH moved AS (
  DELETE FROM events
  WHERE occurred_at < now() - interval '90 days'
  RETURNING *
)
INSERT INTO events_archive SELECT * FROM moved;

-- Insert a parent and its children together
WITH new_order AS (
  INSERT INTO orders (customer_id, total) VALUES ($1, $2) RETURNING id
)
INSERT INTO order_items (order_id, sku, qty)
SELECT new_order.id, i.sku, i.qty
FROM new_order, jsonb_to_recordset($3::jsonb) AS i(sku text, qty int)
RETURNING *;

The trap: all sub-statements see the same snapshot, so a data-modifying CTE cannot observe another one's changes. In the archive example the outer INSERT reads moved, the returned rows, and not the table. Two CTEs updating the same row have undefined order. Sub-statement execution order is also not guaranteed beyond the data dependencies you create through RETURNING.