CTEs (WITH)

TL;DR

A Common Table Expression (CTE) is a named subquery declared with WITH … AS (…). You then reference the name in the main query, or in later CTEs in the same WITH block. Same expressive power as a derived table in FROM, but readable.

The case for CTEs is almost entirely about readability and reuse. A five-step analytics query written as five named CTEs reads top-to-bottom like a recipe. The same query written as nested derived tables reads from the inside out and is famously hard to debug.

The case against: in some older versions of Postgres (≤ 11), CTEs were an optimization fence — the planner would always materialize them, even when inlining would have been faster. Postgres 12+ inlines non-recursive, non-side-effecting CTEs by default; you can override with MATERIALIZED / NOT MATERIALIZED. In Snowflake/BigQuery, CTEs are inlined and free.

A worked example — CTE chain vs nested subqueries

Goal. Per country, the average lifetime spend of users who have placed at least 3 orders.

As nested subqueries (the “wall of parens”):

SELECT country, AVG(lifetime_spend) AS avg_spend
FROM   (SELECT u.country, t.lifetime_spend
        FROM   users u
        JOIN   (SELECT user_id, SUM(total) AS lifetime_spend, COUNT(*) AS n
                FROM   orders
                GROUP  BY user_id) t
               ON t.user_id = u.id
        WHERE  t.n >= 3) x
GROUP  BY country;

As CTEs (the “recipe”):

WITH user_totals AS (
    SELECT user_id,
           SUM(total)  AS lifetime_spend,
           COUNT(*)    AS n_orders
    FROM   orders
    GROUP  BY user_id
),
active_users AS (
    SELECT u.country, ut.lifetime_spend
    FROM   users u
    JOIN   user_totals ut ON ut.user_id = u.id
    WHERE  ut.n_orders >= 3
)
SELECT country, AVG(lifetime_spend) AS avg_spend
FROM   active_users
GROUP  BY country;

Same query, same plan (in modern Postgres). The CTE form reads top-down: “first compute totals, then keep active users, then aggregate”. The nested form reads inside-out and forces you to mentally unwind parens.

CTE syntax

WITH name1 AS (SELECT …),
     name2 AS (SELECT …),
     name3 AS (SELECT … FROM name1 JOIN name2 …)
SELECT * FROM name3;

Each CTE can reference earlier CTEs in the same WITH block. They cannot reference later ones (that’s what recursive CTEs use a different mechanism for; see SQL 204).

CTEs can also wrap data-modifying statements:

WITH archived AS (
    DELETE FROM events WHERE ts < '2025-01-01'
    RETURNING *
)
INSERT INTO events_archive SELECT * FROM archived;

This is the canonical “move rows from A to B atomically” pattern in Postgres.

Multiple references — when materialization helps

WITH heavy AS MATERIALIZED (
    SELECT user_id, COUNT(*) AS n
    FROM   massive_event_table
    WHERE  event_type = 'foo'
    GROUP  BY user_id
)
SELECT * FROM users u JOIN heavy h ON h.user_id = u.id WHERE h.n > 100
UNION ALL
SELECT * FROM heavy WHERE n = 0;

The CTE is referenced twice. With MATERIALIZED, Postgres computes it once and reuses the result. Without, it runs the underlying scan twice. For expensive CTEs referenced multiple times, MATERIALIZED is the right hint.

For CTEs referenced once, NOT MATERIALIZED (or the default in PG12+) lets the planner inline the CTE and push down predicates from the outer query — usually faster.

Dialect notes.

• Postgres 11 and earlier: CTEs were always materialized. WHERE conditions in the outer query couldn’t push into the CTE, leading to surprising slowness. The “fix” was to write a derived table instead.
• Postgres 12+: defaults to inlining; explicit MATERIALIZED / NOT MATERIALIZED keywords let you override.
• Snowflake, BigQuery, DuckDB: always inline non-recursive CTEs.
• MySQL 8+: supports CTEs; older MySQL doesn’t.

CTEs vs subqueries vs views

A CTE that you write the same way in five different queries is begging to be a view. A view that’s expensive to recompute on every query is begging to be a materialized view (or a dbt model).

The “step-by-step analytics” template

This is what most production analytics CTEs look like. Each step is a small, named relation:

WITH
-- 1. raw events filtered to window
raw_events AS (
    SELECT user_id, event, ts
    FROM   events
    WHERE  ts >= CURRENT_DATE - INTERVAL '30 days'
),
-- 2. session boundaries (gap > 30 min = new session)
sessions AS (
    SELECT user_id, ts, event,
           SUM(CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
                         > INTERVAL '30 minutes'
                    THEN 1 ELSE 0 END)
                OVER (PARTITION BY user_id ORDER BY ts) AS session_id
    FROM   raw_events
),
-- 3. one row per session with aggregates
session_summaries AS (
    SELECT user_id, session_id,
           MIN(ts)                           AS started_at,
           MAX(ts) - MIN(ts)                 AS duration,
           BOOL_OR(event = 'purchase')       AS converted
    FROM   sessions
    GROUP  BY user_id, session_id
),
-- 4. per-user features
features AS (
    SELECT user_id,
           COUNT(*)                                     AS sessions,
           AVG(EXTRACT(EPOCH FROM duration))            AS avg_duration_s,
           AVG(CASE WHEN converted THEN 1.0 ELSE 0 END) AS conv_rate
    FROM   session_summaries
    GROUP  BY user_id
)
SELECT * FROM features;

Five small CTEs, each understandable in isolation. Try writing this with nested subqueries — you’ll have a 200-character single line that nobody reviews properly.

Common pitfalls

• Assuming CTE = optimization fence. True in old Postgres, false in PG 12+. Check your version. If in doubt, EXPLAIN and see whether the predicate from the outer query was pushed in.
• Naming conflicts with real tables. WITH users AS (…) SELECT * FROM users is legal — the CTE shadows the table. Confusing in code review. Name CTEs distinctly (u_filtered, users_active).
• Side effects in WITH. Postgres CTE chains with INSERT/UPDATE/DELETE run all branches even if the main query doesn’t reference them. Order matters less than you think; isolation matters more.
• Forgetting CTEs are evaluated once per query, not per row. A CTE doesn’t magically re-evaluate against the current row of the outer query — that’s what correlated subqueries do.
• Using a CTE when a window function suffices. Sometimes a single OVER clause replaces a CTE-and-self-join.

Production patterns for ML

1. Modular feature pipelines. A 200-feature warehouse table is built from a chain of small CTEs, one per feature group:

WITH
  base_users     AS (SELECT id FROM users WHERE …),
  spend_features AS (SELECT user_id, … FROM orders WHERE … GROUP BY user_id),
  click_features AS (SELECT user_id, … FROM events WHERE … GROUP BY user_id),
  attr_features  AS (SELECT user_id, … FROM attributions WHERE … GROUP BY user_id)
SELECT b.*,
       COALESCE(s.spend_30d, 0)   AS spend_30d,
       COALESCE(c.clicks_30d, 0)  AS clicks_30d,
       COALESCE(a.last_source, '') AS last_source
FROM   base_users b
LEFT   JOIN spend_features s USING (user_id)
LEFT   JOIN click_features c USING (user_id)
LEFT   JOIN attr_features  a USING (user_id);

Each CTE is independently testable. dbt builds models exactly this way.

2. Idempotent dedup before inserting. Many warehouse loads use a CTE to define “the row I want to keep” before merging:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY ingested_at DESC) AS rn
    FROM   staging_events
)
INSERT INTO clean_events SELECT * FROM ranked WHERE rn = 1;

The CTE is the keep-only-latest filter; the INSERT is the write. Two named steps beat one nested mess.

Resources

• PostgreSQL — WITH queries — postgresql.org/docs
• Mode Analytics — CTEs — mode.com/sql-tutorial (lessons cover WITH).
• dbt — modeling with CTEs — docs.getdbt.com
• Use The Index, Luke — use-the-index-luke.com — when CTEs become slow it’s usually about predicate pushdown / indexing.