Subqueries, CTEs and window functions

Scalar, correlated and derived subqueries, WITH and recursive CTEs, and the ROW_NUMBER/RANK/LAG family that ranks and compares rows without collapsing them.

Subqueries in four positions

A subquery is a SELECT used as a value, as a filter, or as a table. Where you put it determines what it may return: one row, one column, or a full result set.

-- scalar subquery: one row, one column, usable as an expression
SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- existence test: stops at the first matching row
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- anti-join: customers with nothing
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- derived table: a subquery you can join and filter
SELECT t.customer_id, t.total
FROM (
  SELECT customer_id, SUM(total) AS total
  FROM orders
  GROUP BY customer_id
) AS t
WHERE t.total > 1000;

CTEs and recursion

WITH monthly AS (
  SELECT date_trunc('month', placed_at) AS month,
         SUM(total) AS revenue
  FROM orders
  GROUP BY 1
),
growth AS (
  SELECT month,
         revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly
)
SELECT month, revenue, revenue - prev_revenue AS change
FROM growth
ORDER BY month;

-- recursive: walk up a chain of managers
WITH RECURSIVE chain AS (
  SELECT id, manager_id, name, 1 AS depth
  FROM employees
  WHERE id = 42
  UNION ALL
  SELECT e.id, e.manager_id, e.name, c.depth + 1
  FROM employees e
  JOIN chain c ON e.id = c.manager_id
)
SELECT depth, name FROM chain ORDER BY depth;

• A CTE is a named building block: later CTEs may reference earlier ones, and the final SELECT reads like a sequence of steps.
• Name CTEs after the thing they contain (monthly, active_customers), not after their position (cte1).
• WITH RECURSIVE needs three parts: a base case, a step that consumes the previous result, and a condition that eventually stops producing rows.
• Push filters into the first CTE when you can — fewer rows flowing through every later step is the cheapest optimisation available.

Window functions

An aggregate collapses rows into groups; a window function keeps every row and adds a value computed over a window of related rows. That is what makes ranking, running totals and row-to-row comparisons ordinary SQL.

SELECT
  order_id,
  customer_id,
  total,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS seq,
  RANK()       OVER (ORDER BY total DESC)                              AS rank_all,
  LAG(total)   OVER (PARTITION BY customer_id ORDER BY placed_at)      AS prev_total,
  SUM(total)   OVER (PARTITION BY customer_id)                         AS customer_total,
  AVG(total)   OVER (ORDER BY placed_at
                     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)         AS moving_avg_3
FROM orders;

-- the top order per customer: filter outside the window
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS seq
  FROM orders
)
SELECT * FROM ranked WHERE seq = 1;

• Windows are evaluated after WHERE and GROUP BY but before ORDER BY and LIMIT, so a window result cannot be filtered in the same query level.
• The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW once you add ORDER BY — write the frame out when you mean ROWS.
• PARTITION BY restarts the calculation per group; forgetting it produces one global running total across unrelated entities.
• An index matching the PARTITION BY and ORDER BY columns lets the engine avoid a sort.

FAQ

Related

Data types, NULL and three-valued logic Query tuning and execution plans

Last refreshed 2026-09-18.