CTE basics: naming a subquery

A CTE is a named subquery

When you name the messy piece of a query, the whole thing suddenly becomes reviewable — and that’s exactly what a CTE does. A common table expression (CTE) is a temporary, named result set defined in a WITH clause that exists only for the single statement attached to it. Syntactically it is just a subquery you gave a name. The payoff is structural: instead of nesting logic inward, you lay it out as a sequence of named steps, then the final query reads from them.

Compare the same intent — “managers whose team placed more than 100 orders last month” — written two ways against employees(id, manager_id, name) and an orders table:

-- Nested: read inside-out, and the inner aggregate is buried
SELECT e.name, t.order_count
FROM employees e
JOIN (
  SELECT o.handled_by, count(*) AS order_count
  FROM orders o
  WHERE o.created_at >= date_trunc('month', now()) - interval '1 month'
    AND o.created_at <  date_trunc('month', now())
  GROUP BY o.handled_by
) t ON t.handled_by = e.id
WHERE t.order_count > 100;

-- Same query, top-down pipeline
WITH last_month AS (
  SELECT o.handled_by, count(*) AS order_count
  FROM orders o
  WHERE o.created_at >= date_trunc('month', now()) - interval '1 month'
    AND o.created_at <  date_trunc('month', now())
  GROUP BY o.handled_by
)
SELECT e.name, lm.order_count
FROM last_month lm
JOIN employees e ON e.id = lm.handled_by
WHERE lm.order_count > 100;

The two queries are logically the same. The second one names the messy part (last_month), so the final SELECT reads like a sentence. You debug a CTE by running it on its own — paste just the inner SELECT into a session and inspect its rows — which is far harder with a deeply nested derived table buried in a FROM.

Chaining: each CTE sees the ones before it

You can define several CTEs in one WITH, separated by commas. Every CTE can reference any CTE defined earlier in the same WITH clause, so you build a pipeline where step three reads from steps one and two. This is the real reason seniors reach for CTEs on analytics work: the query becomes a readable chain of transformations instead of a pyramid.

WITH recent_orders AS (
  SELECT handled_by, amount
  FROM orders
  WHERE created_at >= now() - interval '30 days'
),
per_employee AS (                       -- reads from recent_orders
  SELECT handled_by, sum(amount) AS revenue, count(*) AS n
  FROM recent_orders
  GROUP BY handled_by
),
ranked AS (                             -- reads from per_employee
  SELECT handled_by, revenue, n,
         revenue / NULLIF(n, 0) AS avg_ticket
  FROM per_employee
  WHERE n >= 10
)
SELECT e.name, r.revenue, r.avg_ticket
FROM ranked r
JOIN employees e ON e.id = r.handled_by
ORDER BY r.revenue DESC;

The direction matters. A CTE cannot reference a CTE declared after it — that would be a forward reference, and Postgres rejects it — with one special exception: a CTE referencing itself, which is recursion, the subject of lesson 04. For now, order your CTEs so each one only depends on earlier ones.

What CTEs buy you, and what they don’t

The win is readability and reuse-within-a-statement: name a result once, reference it by name several times, and you never duplicate the same subquery text. CTEs do not persist — the name vanishes the instant the statement finishes. If you need a reusable named query across many statements, that is a VIEW, which lesson 02 contrasts with CTEs. And historically a CTE was an optimization fence that changed how Postgres ran your query; that behavior changed in Postgres 12, and lesson 03 is entirely about it. For this lesson, treat a CTE as a pure readability tool: same result, clearer shape.