SQL CTEs Explained: How to Write Cleaner Queries for Complex Analysis

The syntax takes about a minute to learn. WITH name AS (SELECT ...), then query the name. That is not why analysts get CTEs wrong.

They get them wrong in two directions. Some write a hundred line query as one nested subquery pyramid because CTEs "are slower", which is not a claim that holds uniformly across engines. Others chain twelve CTEs where four would do, treating the construct as free abstraction, and produce a query nobody can debug because no single step is testable in isolation.

A CTE is a named subquery scoped to one statement. That definition contains everything useful about it: the naming is the benefit, the single statement scope is the limit, and neither one says anything about speed.

This article covers refactoring a nested query into a readable chain, the naming and structuring decisions that make the chain debuggable, how CTEs actually behave across engines, the window function pattern that makes them unavoidable, recursive CTEs, and the point at which a CTE chain is telling you to build something more permanent.

The running example is a monthly cohort retention query on an orders table.

What a CTE Is, and Three Things It Is Not

A CTE names an intermediate result inside a single SQL statement. Everything else people believe about CTEs is engine-specific or wrong.

Three corrections worth making early.

A CTE is not a temporary table. It exists for the duration of one statement. The next statement in your script cannot see it, it has no indexes, it collects no statistics of its own in most engines, and it is not written to disk as a durable object you can inspect.

A CTE is not automatically faster or slower. Whether it materialises the intermediate result or gets folded into the surrounding query is an optimiser decision that differs by engine and version. Performance claims about CTEs in general are almost always claims about one specific engine.

A CTE is not a view. A view is a stored object other queries can reference. A CTE disappears when the statement finishes. If two dashboards need the same logic, a CTE copied into both is duplication, not reuse.

What a CTE genuinely gives you is a name for a step, and the ability for later steps to reference earlier ones. That turns a query from a structure you read inside out into one you read top to bottom.

The Refactor: From Nesting to a Named Chain

Here is the shape most analysts inherit. Cohort retention written as nested subqueries:

-- The version that is technically correct and painful to modify.
SELECT
    m.cohort_month,
    m.order_month,
    c.cohort_customers,
    COUNT(DISTINCT m.customer_id) AS active_customers
FROM (
    SELECT b.customer_id, f.cohort_month, b.order_month
    FROM (
        SELECT customer_id, date_trunc('month', order_ts) AS order_month
        FROM orders
        WHERE order_status = 'completed'
    ) b
    JOIN (
        SELECT customer_id, MIN(date_trunc('month', order_ts)) AS cohort_month
        FROM orders
        WHERE order_status = 'completed'
        GROUP BY customer_id
    ) f ON f.customer_id = b.customer_id
) m
JOIN (
    SELECT cohort_month, COUNT(*) AS cohort_customers
    FROM (
        SELECT customer_id, MIN(date_trunc('month', order_ts)) AS cohort_month
        FROM orders
        WHERE order_status = 'completed'
        GROUP BY customer_id
    ) f2
    GROUP BY cohort_month
) c ON c.cohort_month = m.cohort_month
GROUP BY m.cohort_month, m.order_month, c.cohort_customers;

The filter order_status = 'completed' appears three times. The first-purchase logic appears twice, and if someone edits one copy the query silently produces wrong retention rather than an error. You have to read from the innermost parentheses outward to understand it.

The same logic as a chain:

-- PostgreSQL syntax. date_trunc('month', ts) is DATE_TRUNC(ts, MONTH) in BigQuery;
-- USING is not supported in SQL Server, which requires an explicit ON clause.
WITH base_orders AS (
    SELECT
        customer_id,
        order_id,
        order_total,
        date_trunc('month', order_ts) AS order_month
    FROM orders
    WHERE order_status = 'completed'
      AND order_ts >= DATE '2024-01-01'
),

first_purchase AS (
    SELECT
        customer_id,
        MIN(order_month) AS cohort_month
    FROM base_orders
    GROUP BY customer_id
),

monthly_activity AS (
    SELECT
        b.customer_id,
        f.cohort_month,
        b.order_month
    FROM base_orders b
    JOIN first_purchase f USING (customer_id)
),

cohort_sizes AS (
    SELECT
        cohort_month,
        COUNT(*) AS cohort_customers
    FROM first_purchase
    GROUP BY cohort_month
)

SELECT
    m.cohort_month,
    m.order_month,
    c.cohort_customers,
    COUNT(DISTINCT m.customer_id) AS active_customers,
    ROUND(100.0 * COUNT(DISTINCT m.customer_id) / c.cohort_customers, 1) AS retention_pct
FROM monthly_activity m
JOIN cohort_sizes c USING (cohort_month)
GROUP BY m.cohort_month, m.order_month, c.cohort_customers
ORDER BY m.cohort_month, m.order_month;

The status filter now exists once. The first-purchase definition exists once and is referenced twice. The reading order matches the thinking order.

The debugging property is the one that pays off daily. To check first_purchase, replace the final SELECT with SELECT * FROM first_purchase LIMIT 20 and run it. Every step in the chain is independently inspectable without restructuring anything. In the nested version, isolating that same logic means manually extracting a subquery from the middle of the pyramid.

Structuring the Chain

A chain is only clearer than nesting if it is structured deliberately. Four rules carry most of the benefit.

One CTE, one transformation. If you cannot describe what a CTE produces in a short phrase, it is doing two things and should be two steps. If a step is only three lines and used once, it may not need to be a step at all.

Name the output, not the operation. first_purchase and cohort_sizes say what the result contains. step2, temp_data, and cte_join say nothing, and a reader has to execute the query mentally to find out. Prefix conventions such as stg_ for lightly cleaned source data and agg_ for aggregates help once a chain passes three or four steps.

Filter as early as the logic allows. Putting the row restriction in the first CTE means every downstream step operates on less data, and it makes the scope of the analysis explicit at the top of the query rather than buried in the final WHERE. Optimisers often push predicates down on their own, but writing it explicitly documents intent regardless of what the planner does.

Keep the chain shallow enough to hold in your head. There is no correct number, but when a query has more CTEs than you can name from memory, the problem has usually outgrown a single statement. That signal is worth acting on rather than absorbing.

For the wider query patterns that these chains are built from (joins, aggregation, window functions, and set operations), the essential SQL skills and query guide for data analysts covers the underlying material this article assumes.

How CTEs Actually Behave: Materialisation and Engine Differences

Whether a CTE is computed once and reused, or re-evaluated at each reference, depends on your engine and your version. This is the single most important operational fact about CTEs and the one most often stated incorrectly.

In the running example, base_orders is referenced twice: once by first_purchase and once by monthly_activity. Depending on the engine, that scan may happen once or twice.

The landscape as it stands:

The PostgreSQL documentation covers the inlining rules and the MATERIALIZED keyword, and the MySQL documentation covers the version boundary, which matters if your organisation still runs 5.7 anywhere.

The practical implications are short. If a CTE is expensive and referenced several times, check the query plan rather than assuming reuse, and if the engine re-evaluates it, promote it to a temporary table. If a CTE is cheap and referenced once, the naming costs you nothing. And never justify a CTE decision to a colleague with a general performance claim; the claim only makes sense with the engine named.

Reading the plan is the skill that settles these arguments. EXPLAIN (or EXPLAIN ANALYZE, which actually executes) shows whether the intermediate was scanned once or repeatedly, and the underlying storage and execution concepts behind those plans are covered in the primer on what a DBMS is and how it works.

The Pattern That Makes CTEs Unavoidable

A window function cannot be referenced in the WHERE clause of the same SELECT that computes it. Window functions are evaluated after WHERE and GROUP BY, so the alias does not exist yet at filter time. This is not a limitation you can work around with parentheses; it requires a wrapper.

-- This fails: order_seq does not exist when WHERE is evaluated.
SELECT
    customer_id,
    order_id,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts) AS order_seq
FROM base_orders
WHERE order_seq = 1;

The CTE version works because the window function has been fully evaluated by the time the outer query filters:

WITH ranked_orders AS (
    SELECT
        customer_id,
        order_id,
        order_ts,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts) AS order_seq
    FROM base_orders
)
SELECT customer_id, order_id, order_ts
FROM ranked_orders
WHERE order_seq = 1;      -- first order per customer

This is the deduplication idiom, the "latest record per group" idiom, and the "top N per category" idiom, all of which are the same shape. It is worth recognising on sight.

One engine note: BigQuery and Snowflake provide a QUALIFY clause that filters on window results directly and removes the need for the wrapper. PostgreSQL and MySQL do not have QUALIFY, so the CTE pattern is the portable form. The PostgreSQL window functions documentation sets out the evaluation order that causes the original error.

Funnel and journey queries lean on this pattern constantly, since almost every step involves picking a first or last event per session, and the surrounding analysis is covered in the guide to finding where customers drop off in a funnel.

Recursive CTEs: Date Spines and Hierarchies

A recursive CTE references itself. It has three required parts: an anchor query that produces the starting rows, a recursive query that produces the next set from the previous one, and a termination condition that eventually returns no rows.

-- PostgreSQL. MySQL 8 and SQLite also use WITH RECURSIVE;
-- SQL Server uses WITH without the RECURSIVE keyword.
WITH RECURSIVE date_spine AS (
    SELECT DATE '2026-01-01' AS d                 -- anchor
    UNION ALL
    SELECT (d + INTERVAL '1 day')::date           -- recursive term
    FROM date_spine
    WHERE d < DATE '2026-12-31'                   -- termination
)
SELECT d FROM date_spine;

Two cautions. UNION ALL is standard here because UNION deduplicates on every iteration at a cost. And a missing or incorrect termination condition produces runaway recursion; MySQL enforces a configurable recursion depth limit and SQL Server offers MAXRECURSION, but relying on the engine's guard rail rather than writing a correct stopping condition is a bad habit.

Where a simpler built-in exists, prefer it. PostgreSQL's generate_series produces a date spine in one line, and BigQuery has GENERATE_DATE_ARRAY. The recursive form is the portable fallback and the right tool when the problem is genuinely hierarchical:

-- Simpler where available (PostgreSQL).
SELECT generate_series(DATE '2026-01-01', DATE '2026-12-31', INTERVAL '1 day')::date AS d;

The genuinely recursive use cases are graph shaped: an org chart where you need every report under a manager at any depth, a category tree, or a bill of materials. Those cannot be expressed with a fixed number of joins, because the depth is a property of the data rather than of the query. If your hierarchy has a known maximum depth and it is small, explicit joins are often clearer.

The SQLite documentation is a useful reference for the recursive syntax in a minimal engine, which makes the required structure easier to see than in a larger dialect.

When the Chain Is Telling You to Build Something Else

A CTE chain that gets copied into a second query has stopped being a query structure and started being a missing data model.

The signals are consistent. The same four CTEs appear at the top of several analysts' queries. A definition changes and three dashboards disagree for a week. A query that started at forty lines is now three hundred and takes minutes to run because it rebuilds the same intermediate every time.

The options, in increasing order of commitment:

The move that solves most of these cases is promoting the shared prefix of the chain into a model that is built once and queried by everyone. That is precisely the problem transformation tooling exists to solve, and the dbt documentation describes the model, test, and dependency structure involved. The relevant point for an analyst is that the SQL barely changes: a CTE becomes a model file, and downstream queries reference it by name instead of redefining it.

Temporary tables deserve a specific mention because they solve a problem CTEs cannot. If an intermediate result is large and joined repeatedly, writing it to a temp table lets you index it, which a CTE never permits. The cost is that the logic now spans multiple statements and the cleanup is your responsibility.

Being able to explain why a query is structured a particular way is also, in practice, an interview skill: walking through a CTE chain step by step is a far stronger answer than presenting a nested query and hoping nobody asks. The framing patterns for that are in the guide to explaining a data analyst project in an interview.

Where to Go From Here

For the query fundamentals these chains are assembled from, including joins, aggregation, and set operations, the essential SQL skills and query guide for data analysts is the direct prerequisite.

For reading query plans and understanding why an engine chose to materialise or inline an intermediate, the concepts in what a DBMS is and how it works cover the storage and execution model underneath.

For the funnel and sequencing analyses where the window-function-inside-a-CTE pattern appears constantly, see finding where customers drop off in a funnel.

And for the point where a transformation chain outgrows SQL entirely and belongs in a scripted workflow, the introduction to pandas for analysts covers the alternative.