Skip to Content

CTEs & Subqueries

When to use

Break a long, deep query into readable, named stages — especially for feature pipelines with several filter+aggregate steps.

Analogy

Subqueries are nested footnotes; CTEs are labelled chapters you can reference from anywhere in the query.

Data-flow diagram

  WITH
    stage1 AS (SELECT ...),     -- chapter 1
    stage2 AS (SELECT ...        -- chapter 2: depends
                 FROM stage1),   -- on chapter 1
    stage3 AS (SELECT ...
                 FROM stage2     -- chapter 3
                 JOIN other)
  SELECT * FROM stage3;

Deep explanation

CTEs (WITH … AS) name subqueries at the top of a statement. They make complex queries readable, allow re-use across multiple references in the same query, and let you form recursive queries. The optimizer inlines most non-recursive CTEs by default; for very large intermediate result sets that you reference several times, materialize manually with WITH … AS MATERIALIZED (Postgres 12+) to avoid recomputation.

Examples

Example 1

-- 6a: pipeline of named stages (readable)
WITH
  recent AS (
    SELECT user_id, ts, amount
      FROM events
     WHERE ts >= NOW() - INTERVAL '30 days'
  ),
  per_user AS (
    SELECT user_id,
           COUNT(*)             AS events,
           SUM(amount)          AS spend,
           AVG(amount)          AS avg_spend
      FROM recent
     GROUP BY user_id
  )
SELECT * FROM per_user WHERE events >= 5;

Three named stages read top-to-bottom, eliminating the nested-brain-cost of correlated subqueries.

Example 2

-- 6b: recursive CTE — flights graph, hierarchy, dependency chain
WITH RECURSIVE chain AS (
  SELECT id, parent_id, 1 AS depth
    FROM tasks WHERE parent_id IS NULL
  UNION ALL
  SELECT t.id, t.parent_id, c.depth + 1
    FROM tasks t JOIN chain c ON t.parent_id = c.id
)
SELECT * FROM chain;

WITH RECURSIVE turns graph traversal into a single SQL statement — useful for tool/agent dependency maps.

Example 3

-- 6c: materializing a CTE for re-use (avoid two scans)
WITH active_users AS MATERIALIZED (
  SELECT user_id FROM events WHERE ts >= NOW() - INTERVAL '7 days'
)
SELECT a.user_id, COUNT(e.event_id)
  FROM active_users a
  LEFT JOIN events e USING (user_id)
 GROUP BY a.user_id;

AS MATERIALIZED forces the planner to compute the CTE once and treat it like a temp table — saves time when referenced multiple times.

Common mistake

Believing CTEs magically optimize. In Postgres, by default a non-MATERIALIZED CTE is inlined twice if referenced twice — sometimes the planner chooses a worse plan than a subquery. For ML pipelines with heavy intermediates, MATERIALIZED is often faster. Also: recursive CTEs without UNION ALL termination clause can infinite-loop.

Key takeaway

Use CTEs for readability; with RECURSIVE for trees/graphs; AS MATERIALIZED for large intermediates referenced multiple times; CTE boundary doesn’t change the result, but does change planner choices.

Production Failure Playbook

Failure scenario 1: cte-inlined-twice-quietly

Failure scenario 2: recursive-cte-no-termination