Subqueries, CTEs and Window Functions

In lesson 2 every aggregate collapsed many rows into one. Real questions need more steps: "customers who spent more than the average", "each customer's biggest order", "revenue growing month by month". You answer them by building a query in layers. This lesson shows the three tools for layering: subqueries, CTEs and window functions. It uses the shop from lesson 0.

Words used in this lesson

• A subquery is a SELECT written inside another statement, in parentheses.
• A scalar subquery returns exactly one row with one column, so you can use it like a single value.
• A correlated subquery uses a column of the outer query, so it is re-evaluated for each outer row (in theory; the planner often does better).
• A CTE (common table expression) is a named query defined with WITH name AS (...). Think of it as a temporary name that lives for one statement.
• A recursive CTE is a CTE that refers to itself. It is how SQL walks trees and chains.
• A window function computes a value for each row using a set of related rows (its window), without collapsing them.
• PARTITION BY splits the rows into independent groups for a window function. ORDER BY inside OVER orders the rows within each one.
• A frame is the part of the partition that one row looks at, for example "all rows from the start up to me".
• Materialised means the result is computed once and stored (in memory or on disk), then read from there.

Subqueries

Scalar subquery: one value

A subquery that returns one row and one column can stand where a value stands. Which products cost more than the average price?

SELECT name, price FROM productsWHERE price > (SELECT avg(price) FROM products)ORDER BY price;

    name    | price ------------+------- Desk lamp  | 24.90 Backpack   | 45.00 Headphones | 59.00(3 rows)

The inner query runs once and gives 23.93. If it returned two rows, PostgreSQL stops with ERROR: more than one row returned by a subquery used as an expression (I ran SELECT (SELECT id FROM orders WHERE status = 'paid') to see it). A scalar subquery that finds no row gives NULL, not an error. That is useful but easy to forget.

IN: is the value in this list?

IN (subquery) is true when the value matches any row the subquery returns. Customers who have a paid order:

SELECT name FROM customersWHERE id IN (SELECT customer_id FROM orders WHERE status = 'paid')ORDER BY name;

 name ------ Asha Ben(2 rows)

Asha has two paid orders, but she shows once: IN only asks "is there a match", it does not multiply rows the way a join does. That is the main reason to prefer it over a join when you only need to filter.

EXISTS: is there at least one row?

EXISTS (subquery) is true if the subquery returns any row at all. The columns it selects do not matter, so people write SELECT 1.

SELECT c.name FROM customers cWHERE EXISTS (SELECT 1 FROM orders o              WHERE o.customer_id = c.id AND o.status = 'shipped');

This returns Asha and Chen, the two customers with a shipped order.

Which should you use, IN or EXISTS? For "has a match" they give the same rows, and PostgreSQL usually plans both as a semi join (stop at the first match). The real difference is NOT. NOT IN breaks when the list contains NULL (lesson 0 and lesson 1 showed it return zero rows). NOT EXISTS does not. So: use EXISTS / NOT EXISTS by default.

Correlated subqueries

In the EXISTS example the inner query used c.id from the outer query. That makes it correlated: it cannot run on its own, because it needs the current outer row. You can also put one in the SELECT list, for example (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) gives each customer's order count (3, 1, 2, 1, 0, 0). Here is one in WHERE, the "most expensive product in each category" question:

SELECT p.name, p.category, p.price FROM products pWHERE p.price = (SELECT max(p2.price) FROM products p2 WHERE p2.category = p.category)ORDER BY p.category;

    name    |  category  | price ------------+------------+------- Headphones | audio      | 59.00 Backpack   | bags       | 45.00 Desk lamp  | home       | 24.90 Gift card  | other      | 10.00 Notebook   | stationery | 3.50(5 rows)

Read it as a loop: for each product, find the highest price in its own category, keep the product if it matches. A correlated subquery in SELECT can be slow on big tables, because naively it runs once per row. A join to a grouped query, or a window function (below), usually does the same in one pass. Measure with EXPLAIN before you worry (lesson 5).

CTEs: naming the steps

A CTE moves a subquery to the top and gives it a name. The query then reads like a recipe. Order totals first, then use them:

WITH order_totals AS (  SELECT order_id, sum(quantity * unit_price) AS total  FROM order_items GROUP BY order_id)SELECT o.id, c.name, o.status, t.totalFROM orders oJOIN customers c ON c.id = o.customer_idJOIN order_totals t ON t.order_id = o.idORDER BY o.id;

 id | name |  status   | total  ----+------+-----------+--------  1 | Asha | paid      |  13.00  2 | Asha | shipped   |  59.00  3 | Ben  | paid      |  48.50  4 | Chen | cancelled |  24.90  5 | Chen | shipped   |  22.50  6 | Asha | paid      | 106.40  7 | Dara | new       |   3.50(7 rows)

Summing the order lines before the join also avoids the fan-out bug from lesson 1 and lesson 2. You can chain CTEs with commas (WITH a AS (...), b AS (... FROM a ...)), and a later one can use an earlier one. I ran a chain that adds up each customer's non-cancelled spending: Asha 178.40, Ben 48.50, Chen 22.50, Dara 3.50, and Eli and Fay 0 through a LEFT JOIN and coalesce.

Subquery or CTE? They mean the same. Pick the CTE when you use a step more than once or when the nesting gets deep. Pick a plain subquery for a tiny one-off filter.

Are CTEs materialised? It depends on the version

This is a favourite interview question, and the answer changed. In PostgreSQL 11 and older, a CTE was always computed on its own, like a wall the planner could not see through ("an optimisation fence"). Since PostgreSQL 12, a plain CTE that is not recursive, has no side effects and is used once is inlined: the planner treats it as if you wrote a subquery, so filters can be pushed into it. If a CTE is used more than once, it is still computed once and stored. You can force either with MATERIALIZED or NOT MATERIALIZED. I ran four plans on 16.15 to see it.

A once-used CTE with a filter outside. The CTE disappears from the plan and id = 3 uses the primary key index:

EXPLAIN (COSTS OFF) WITH t AS (SELECT * FROM orders WHERE id = 3) SELECT * FROM t;

               QUERY PLAN               ---------------------------------------- Index Scan using orders_pkey on orders   Index Cond: (id = 3)(2 rows)

The same shape with AS MATERIALIZED. Now the CTE is a separate step (a CTE Scan), and the outer filter id = 3 is applied afterwards on the stored result:

EXPLAIN (COSTS OFF)WITH t AS MATERIALIZED (SELECT * FROM orders WHERE status = 'paid')SELECT * FROM t WHERE id = 3;

                QUERY PLAN                 ------------------------------------------- CTE Scan on t   Filter: (id = 3)   CTE t     ->  Seq Scan on orders           Filter: (status = 'paid'::text)(5 rows)

With NOT MATERIALIZED the same query is inlined again: the plan is Index Scan using orders_pkey with Index Cond: (id = 3) and Filter: (status = 'paid'::text).

A CTE used twice (joined to itself) is computed once and read twice:

 Hash Join   Hash Cond: (a.id = b.id)   CTE t     ->  Seq Scan on orders           Filter: (status = 'paid'::text)   ->  CTE Scan on t a   ->  Hash         ->  CTE Scan on t b

Recursive CTEs: trees and chains

Some data points at itself. customers.referred_by is one example (lesson 1). A product category tree is a better one: Office contains Stationery, which contains Pens. You do not know how deep it goes, so a fixed number of self joins will not work. A recursive CTE repeats until nothing new comes out. Create a small category table:

CREATE TABLE categories (  id        integer PRIMARY KEY,  name      text NOT NULL,  parent_id integer REFERENCES categories (id));INSERT INTO categories VALUES  (1,'Everything',NULL),(2,'Office',1),(3,'Stationery',2),(4,'Notebooks',3),  (5,'Pens',3),(6,'Electronics',1),(7,'Audio',6),(8,'Headphones',7),(9,'Bags',1);

The pattern has two parts joined by UNION ALL: a starting query (the roots), and a step that joins the table to the rows found so far. Build the whole tree with a depth and a path:

WITH RECURSIVE tree AS (  SELECT id, name, parent_id, 0 AS depth, name::text AS path  FROM categories WHERE parent_id IS NULL  UNION ALL  SELECT c.id, c.name, c.parent_id, t.depth + 1, t.path || ' > ' || c.name  FROM categories c JOIN tree t ON c.parent_id = t.id)SELECT repeat('  ', depth) || name AS tree, depth, pathFROM tree ORDER BY path;

       tree       | depth |                     path                      ------------------+-------+----------------------------------------------- Everything       |     0 | Everything   Bags           |     1 | Everything > Bags   Electronics    |     1 | Everything > Electronics     Audio        |     2 | Everything > Electronics > Audio       Headphones |     3 | Everything > Electronics > Audio > Headphones   Office         |     1 | Everything > Office     Stationery   |     2 | Everything > Office > Stationery       Notebooks  |     3 | Everything > Office > Stationery > Notebooks       Pens       |     3 | Everything > Office > Stationery > Pens(9 rows)

How it runs: round 0 finds the root (Everything). Round 1 joins categories to those rows and finds its three children. Round 2 finds their children, and so on. It stops when a round finds no rows. Sorting by path puts children right under their parent.

You can also walk up: start at Headphones and join c.id = up.parent_id. That returned Headphones, Audio, Electronics, Everything (the breadcrumb).

The same pattern on customers follows the referral chain from Dara: Dara (0 hops), Ben (1), Asha (2). I ran it too.

Window functions

An aggregate with GROUP BY returns one row per group. A window function keeps every row and adds a value computed over a window of related rows. The syntax is function(...) OVER (...). The simplest window is the whole table: OVER (). Each order next to the grand total and its share:

CREATE TEMP VIEW ot ASSELECT o.id AS order_id, o.customer_id, o.status, o.ordered_at,       sum(oi.quantity * oi.unit_price) AS totalFROM orders o JOIN order_items oi ON oi.order_id = o.id GROUP BY o.id;SELECT order_id, customer_id, total,       sum(total) OVER () AS grand_total,       round(total * 100 / sum(total) OVER (), 1) AS pctFROM ot ORDER BY order_id;

 order_id | customer_id | total  | grand_total | pct  ----------+-------------+--------+-------------+------        1 |           1 |  13.00 |      277.80 |  4.7        2 |           1 |  59.00 |      277.80 | 21.2        3 |           2 |  48.50 |      277.80 | 17.5        4 |           3 |  24.90 |      277.80 |  9.0        5 |           3 |  22.50 |      277.80 |  8.1        6 |           1 | 106.40 |      277.80 | 38.3        7 |           4 |   3.50 |      277.80 |  1.3(7 rows)

(ot is a temporary view that holds "one row per order with its total", so the next examples stay short. It disappears when you disconnect.) Seven rows in, seven rows out. Inside OVER you can use:

• PARTITION BY x splits the rows into groups. The function restarts in each group. Without it there is one group.
• ORDER BY y orders rows inside each group. It also changes what "up to this row" means.
• A frame (ROWS BETWEEN ...) narrows which rows each row looks at.

ROW_NUMBER, RANK, DENSE_RANK

All three number rows in the window's order. They differ only when there are ties. Order lines per order has ties (three orders have 2 lines). I add order_id to the row_number sort so its result is the same every time:

WITH lines AS (SELECT order_id, count(*) AS n FROM order_items GROUP BY order_id)SELECT order_id, n,       row_number() OVER (ORDER BY n DESC, order_id) AS rn,       rank()       OVER (ORDER BY n DESC) AS rnk,       dense_rank() OVER (ORDER BY n DESC) AS drnkFROM lines ORDER BY n DESC, order_id;

 order_id | n | rn | rnk | drnk ----------+---+----+-----+------        6 | 3 |  1 |   1 |    1        1 | 2 |  2 |   2 |    2        3 | 2 |  3 |   2 |    2        5 | 2 |  4 |   2 |    2        2 | 1 |  5 |   5 |    3        4 | 1 |  6 |   5 |    3        7 | 1 |  7 |   5 |    3(7 rows)

If ORDER BY does not make the order unique, row_number() picks between tied rows in no promised order. Always add a tie-breaker (like the id) when the result must be repeatable.

Windows and PARTITION BY

PARTITION BY customer_id runs the function separately for each customer. Each customer's orders numbered in time order, with a running total of their spending:

SELECT customer_id, order_id, ordered_at::date AS day, total,       row_number() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS nth_order,       sum(total)   OVER (PARTITION BY customer_id ORDER BY ordered_at) AS runningFROM ot ORDER BY customer_id, ordered_at;

 customer_id | order_id |    day     | total  | nth_order | running -------------+----------+------------+--------+-----------+---------           1 |        1 | 2025-01-05 |  13.00 |         1 |   13.00           1 |        2 | 2025-02-11 |  59.00 |         2 |   72.00           1 |        6 | 2025-03-15 | 106.40 |         3 |  178.40           2 |        3 | 2025-02-14 |  48.50 |         1 |   48.50           3 |        4 | 2025-02-20 |  24.90 |         1 |   24.90           3 |        5 | 2025-03-02 |  22.50 |         2 |   47.40           4 |        7 | 2025-03-28 |   3.50 |         1 |    3.50(7 rows)

Asha's running total goes 13, 72, 178.40 and starts again at 3.50 for Dara's only order. Note that sum(...) OVER (ORDER BY ...) is a running total: with an ORDER BY inside OVER, the sum covers the rows from the start of the partition up to the current row.

LAG and LEAD: look at the neighbour

lag(x) is the value from the previous row in the window, lead(x) from the next. Both give NULL at the edge. They answer "what changed since last time?". Asha's orders, with the gap from the previous one:

SELECT order_id, ordered_at::date AS day, total,       lag(total)  OVER w AS prev_total,       lead(total) OVER w AS next_total,       total - lag(total) OVER w AS change,       ordered_at::date - lag(ordered_at::date) OVER w AS days_since_prevFROM ot WHERE customer_id = 1WINDOW w AS (ORDER BY ordered_at)ORDER BY ordered_at;

 order_id |    day     | total  | prev_total | next_total | change | days_since_prev ----------+------------+--------+------------+------------+--------+-----------------        1 | 2025-01-05 |  13.00 |            |      59.00 |        |                        2 | 2025-02-11 |  59.00 |      13.00 |     106.40 |  46.00 |              37        6 | 2025-03-15 | 106.40 |      59.00 |            |  47.40 |              32(3 rows)

WINDOW w AS (...) names a window once so you do not repeat it. lag(total, 1, 0) takes a default for the missing edge: lag(total, 1, 0) gave 0 instead of NULL on the first row. Month-over-month growth is the same shape: lag on the monthly total.

Running totals and frames

A frame says which rows around the current one the function sees. The default when you give an ORDER BY is "from the start of the partition up to the current row and its peers" (peers are rows that tie on the sort key). You can be explicit with ROWS BETWEEN:

SELECT order_id, total,  sum(total) OVER (ORDER BY order_id) AS default_frame,  sum(total) OVER (ORDER BY order_id                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame,  round(avg(total) OVER (ORDER BY order_id                   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 2) AS moving_avg3FROM ot ORDER BY order_id;

 order_id | total  | default_frame | rows_frame | moving_avg3 ----------+--------+---------------+------------+-------------        1 |  13.00 |         13.00 |      13.00 |       36.00        2 |  59.00 |         72.00 |      72.00 |       40.17        3 |  48.50 |        120.50 |     120.50 |       44.13        4 |  24.90 |        145.40 |     145.40 |       31.97        5 |  22.50 |        167.90 |     167.90 |       51.27        6 | 106.40 |        274.30 |     274.30 |       44.13        7 |   3.50 |        277.80 |     277.80 |       54.95(7 rows)

moving_avg3 is the average of the row, the one before and the one after (two rows at the edges). It is the base for a "7-day moving average" on a daily table.

Two traps with the default frame. First, with ties, the default includes all the peers, so a running total jumps. I ran sum(x) OVER (ORDER BY x) on the values 1, 2, 2, 3: it gave 1, 5, 5, 8 (both 2s see each other), while ROWS UNBOUNDED PRECEDING gave the true row-by-row 1, 3, 5, 8. For running totals I always write ROWS explicitly, and add a tie-breaker to the order.

Second, last_value() with the default frame returns the current row, not the last row of the partition, because the frame stops at the current row. On our orders it returned 13.00, 59.00, 48.50 and so on (each row's own total). With ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING every row got 3.50, the real last value.

Top N per group

"Each customer's biggest order", "the 3 best sellers in each category": filter on a rank. The pattern is always the same: number the rows inside each group, wrap it, keep the small numbers.

SELECT * FROM (  SELECT customer_id, order_id, total,         row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn  FROM ot) xWHERE rn <= 1ORDER BY customer_id;

 customer_id | order_id | total  | rn -------------+----------+--------+----           1 |        6 | 106.40 |  1           2 |        3 |  48.50 |  1           3 |        4 |  24.90 |  1           4 |        7 |   3.50 |  1(4 rows)

Change rn <= 1 to rn <= 2 for the top two. Which function you pick matters when there are ties. row_number() gives exactly N rows per group and breaks ties arbitrarily. rank() keeps all rows tied at the border, so you can get more than N. The top two products by units sold per category, with rank(), and a window function over an aggregate (the window runs after GROUP BY):

SELECT * FROM (  SELECT p.category, p.name, sum(oi.quantity) AS units,         rank() OVER (PARTITION BY p.category ORDER BY sum(oi.quantity) DESC) AS rnk  FROM order_items oi JOIN products p ON p.id = oi.product_id  GROUP BY p.category, p.name) xWHERE rnk <= 2ORDER BY category, rnk;

  category  |    name    | units | rnk ------------+------------+-------+----- audio      | Headphones |     2 |   1 bags       | Backpack   |     2 |   1 home       | Desk lamp  |     1 |   1 stationery | Pen        |    17 |   1 stationery | Notebook   |     7 |   2(5 rows)

Two PostgreSQL alternatives. For top 1 per group, DISTINCT ON (customer_id) ... ORDER BY customer_id, total DESC is short and returned the same four rows.

For top N per group, a LATERAL subquery runs once per outer row, so it can use LIMIT. It is often fast when you have an index on the sort column, because each group only reads N rows:

SELECT c.name, t.order_id, t.totalFROM customers cCROSS JOIN LATERAL (  SELECT order_id, total FROM ot  WHERE ot.customer_id = c.id ORDER BY total DESC LIMIT 2) tORDER BY c.id, t.total DESC;

Asha gets orders 6 and 2, Chen gets 4 and 5, and so on (6 rows in all). Eli and Fay vanish because CROSS JOIN LATERAL is an inner join. Use LEFT JOIN LATERAL (...) t ON true to keep them. Another useful window function is ntile(n), which cuts ordered rows into n equal buckets (ntile(2) split our seven orders into 4 "cheap" and 3 "dear").

Try it

Run setup.sql from lesson 0 first (and the categories statements above for question 5).

1. List products with their price and the average price of their category, using a window function (no GROUP BY).
2. For each order, show its total and the rank of the order among all orders by total (biggest = 1).
3. Show each customer's latest order (one row per customer who has orders).
4. Show each month's revenue and the change from the previous month.
5. Using the categories table, list every category under Office (children, grandchildren, ...).

Answers

1. SELECT name, category, price, round(avg(price) OVER (PARTITION BY category), 2) AS category_avg FROM products; Stationery rows show 2.35 (the average of 3.50 and 1.20); the single-product categories show their own price.
2. Using the ot view from this lesson: SELECT order_id, total, rank() OVER (ORDER BY total DESC) AS pos FROM ot ORDER BY pos; Order 6 (106.40) is 1, order 2 (59.00) is 2, order 7 (3.50) is 7.
3. SELECT * FROM (SELECT customer_id, order_id, ordered_at, row_number() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) AS rn FROM ot) x WHERE rn = 1; gives orders 6, 3, 5, 7. DISTINCT ON (customer_id) ... ORDER BY customer_id, ordered_at DESC also works.
4. Sum per month in a CTE (as in lesson 2), then lag: WITH m AS (SELECT date_trunc('month', ordered_at)::date AS month, sum(total) AS revenue FROM ot GROUP BY 1) SELECT month, revenue, revenue - lag(revenue) OVER (ORDER BY month) AS change FROM m ORDER BY month; January has a NULL change. The change is +119.40 in February, then 0.00 in March. Both months total 132.40, which is the coincidence noted in lesson 2.
5. WITH RECURSIVE sub AS (SELECT id, name FROM categories WHERE name = 'Office' UNION ALL SELECT c.id, c.name FROM categories c JOIN sub s ON c.parent_id = s.id) SELECT name FROM sub WHERE name <> 'Office'; gives Stationery, Notebooks, Pens.

Interview questions

Key takeaways

• A scalar subquery gives one value. IN and EXISTS test for matches. Prefer NOT EXISTS over NOT IN.
• A CTE names a step. On PostgreSQL 12 and later a single-use CTE is inlined; a CTE used twice is computed once. MATERIALIZED and NOT MATERIALIZED override this.
• A recursive CTE has a start query and a step joined with UNION ALL. Guard against cycles with CYCLE or a depth limit.
• A window function adds a value to every row: OVER (PARTITION BY ... ORDER BY ...). row_number, rank and dense_rank differ on ties.
• lag and lead read neighbours. For a running total write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• Window functions cannot be used in WHERE. For top N per group, number rows in a subquery and filter outside.

What's next

Lesson 4: Designing a Schema, Keys, Normalisation and Constraints. So far the shop was handed to you. Next you design it: which key type to choose, what a foreign key does on delete, how to split messy data into clean tables, and how to store money and time without surprises. For the Prisma and SQL interview questions see Full-Stack Q&A, Lesson 08.