CTEs and query composition

A common table expression (CTE) is a named, query-scoped subquery you declare with WITH. CTEs are the analyst's most important readability tool — they let you decompose a 100-line query into named steps that read like prose.

From nested subquery to readable pipeline

The same query, two ways. First as a nested subquery — works, but you have to read it inside-out:

SELECT country, AVG(monthly_spend) AS avg_spend
FROM (
  SELECT customer_id, country,
    SUM(amount) AS monthly_spend
  FROM (
    SELECT customer_id, country, DATE_TRUNC('month', order_date) AS m, amount
    FROM orders JOIN customers USING (customer_id)
  ) raw
  GROUP BY customer_id, country
) per_cust
GROUP BY country;

Now the same logic as a CTE pipeline — reads top-to-bottom like steps in a recipe:

WITH enriched_orders AS (
  SELECT customer_id, country, DATE_TRUNC('month', order_date) AS m, amount
  FROM orders
  JOIN customers USING (customer_id)
),
customer_monthly AS (
  SELECT customer_id, country, SUM(amount) AS monthly_spend
  FROM enriched_orders
  GROUP BY customer_id, country
)
SELECT country, AVG(monthly_spend) AS avg_spend
FROM customer_monthly
GROUP BY country;

Materialisation: CTE vs subquery, perf considerations

Historically (and still in older Postgres), CTEs were 'optimisation fences' — the engine materialised the CTE result before the outer query, even when inlining would have been faster. Postgres 12+, MySQL 8+, and most modern engines inline CTEs by default when there are no side effects.

• Postgres 12+ — CTEs are inlined unless WITH … AS MATERIALIZED is specified, or the CTE is referenced more than once, or there's a recursive or non-deterministic component
• MySQL 8+ — inlines aggressively
• Snowflake / BigQuery / DuckDB — CTEs are pure sugar, inlined transparently
• Older Postgres (≤11) — CTEs always materialised; that meant 'WITH x AS (slow_query) SELECT * FROM x' could be 10× slower than the equivalent subquery

Multi-CTE analytical pipelines

The dbt-style 'one CTE per logical step' pattern is the working analyst's house style. Each CTE has one job; the final SELECT is usually a one-liner that pulls from the last CTE.

WITH active_users AS (
  SELECT user_id
  FROM users
  WHERE deleted_at IS NULL AND last_seen_at > NOW() - INTERVAL '30 days'
),
revenue_30d AS (
  SELECT user_id, SUM(amount) AS spend_30d
  FROM orders
  WHERE order_date > NOW() - INTERVAL '30 days'
  GROUP BY user_id
),
feature_use AS (
  SELECT user_id, COUNT(DISTINCT feature) AS features_used
  FROM events
  WHERE event_type = 'feature_used' AND event_date > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT a.user_id,
  COALESCE(r.spend_30d, 0) AS spend_30d,
  COALESCE(f.features_used, 0) AS features_used
FROM active_users a
LEFT JOIN revenue_30d r USING (user_id)
LEFT JOIN feature_use f USING (user_id);