Multiple CTEs — Use multiple CTEs to break down logic - SQL

SECTION 1

Historical Context & Motivation

Relational databases dominated enterprise computing throughout the 1980s and 1990s, yet the SQL language itself evolved slowly. Early SQL queries that required intermediate results had only two options: deeply nested subqueries—often three or four levels deep—or temporary tables that cluttered the database namespace. Both approaches made queries brittle and difficult to reason about. The need for a structured, inline mechanism to name and reuse intermediate result sets motivated the introduction of the Common Table Expression (CTE) into the SQL standard.

1986 • SQL-86 Standardized

The first ANSI SQL standard codified SELECT, FROM, WHERE, and subqueries as the primary tools for data retrieval. Complex logic required deeply nested subqueries or multiple passes with temporary tables.

1999 • SQL:1999 Introduces CTEs

The SQL:1999 standard introduced the WITH clause, enabling Common Table Expressions and recursive queries. This gave developers a way to name intermediate result sets inline, dramatically improving readability.

2003–2011 • Major RDBMS Adoption

PostgreSQL (8.1, 2005), SQL Server (2005), Oracle (9i release 2), and eventually MySQL (8.0, 2018) all shipped CTE support, making multiple CTEs a portable, industry-standard pattern.

2020s • CTEs as Best Practice

Modern data engineering (dbt, analytics engineering) promotes CTE-heavy SQL style as a readability and testing best practice. Multiple CTEs per query are now the norm in production analytics code.

The central question that multiple CTEs answer is straightforward: How can we decompose a complex query into named, sequential stages so that each stage is independently understandable and testable? Whereas a single CTE replaces one subquery, chaining multiple CTEs transforms an entire query pipeline into a series of clearly labeled transformations—much like composing functions in a program.

SECTION 2

Core Principles & Definitions

Before diving into syntax, it is essential to internalize a handful of foundational principles that govern how multiple CTEs operate within a single SQL statement. These principles clarify scope, ordering, and optimization behavior.

Sequential Visibility

Each CTE can reference any CTE defined before it in the WITH clause, but it cannot reference CTEs defined after it. This creates a top-down dependency chain analogous to variable declarations in imperative code.

Single WITH Keyword

Multiple CTEs share a single WITH keyword and are separated by commas. Each CTE is given a unique alias followed by AS and a parenthesized SELECT statement.

Statement-Level Scope

CTE aliases exist only for the duration of the enclosing statement. Once the final SELECT (or INSERT/UPDATE/DELETE) executes, all CTE definitions go out of scope—unlike temporary tables.

Optimizer Freedom

Most modern optimizers inline non-recursive CTEs, meaning the engine may merge, reorder, or push predicates across CTE boundaries. CTEs are a logical abstraction, not a forced materialization point (unless explicitly requested).

Composability

Because later CTEs can reference earlier ones, complex transformations—filtering, aggregating, joining, ranking—can be layered incrementally, keeping each stage's logic focused and testable.

✦

KEY TAKEAWAY

Think of multiple CTEs like stages in a Unix pipeline: each stage receives data, applies one transformation, and passes a clean result to the next stage. Just as cat file | grep ERROR | sort | uniq -c is clearer than a single monolithic script, a chain of CTEs is clearer than a tower of nested subqueries. Each named CTE is a self-documenting checkpoint you can inspect independently.

SECTION 3

Visual Explanation — CTE Pipeline Flow

The diagram below illustrates how a query with three CTEs flows from raw tables through intermediate named result sets to a final output. Each rounded rectangle represents a CTE, and the arrows show data dependencies. Notice how the final SELECT references only the last CTE, but that CTE itself depends on the two before it.

The pipeline begins with two raw tables (orders and customers), flows through three CTEs—each performing a single logical step—and culminates in a final SELECT that returns only the top-ranked customers.

Observe that CTE 1 (recent_orders) handles both filtering and joining—two operations that logically belong together because they define the scope of "recent customer orders." CTE 2 (order_totals) then aggregates that scoped data per customer, and CTE 3 (ranked_customers) applies a window function to rank customers by spending. Each stage transforms the result set in exactly one conceptual way, making the query's intent immediately clear to any reviewer.

SECTION 4

How Multiple CTEs Work — Syntax & Execution

Canonical Syntax

The general pattern for multiple CTEs uses a single WITH keyword followed by comma-separated CTE definitions. Each definition consists of a name, an optional column list, and a parenthesized query. The final statement references one or more of the defined CTEs.

MULTIPLE CTE SYNTAX PATTERN

WITH cte_1 AS (SELECT …), cte_2 AS (SELECT … FROM cte_1 …), cte_3 AS (SELECT … FROM cte_2 …) SELECT … FROM cte_3 …;

Only one WITH keyword is used. Each CTE is separated by a comma. Later CTEs may reference earlier ones. The final SELECT (or DML) follows the last CTE definition.

Execution Semantics

Although the syntax suggests a sequential, top-to-bottom execution model, modern query optimizers treat non-recursive CTEs as inline views by default. The optimizer is free to merge CTE definitions into the outer query, push predicates through CTE boundaries, and reorder joins globally. In PostgreSQL 12+, the MATERIALIZED and NOT MATERIALIZED hints give you explicit control over whether a CTE's result is computed once and stored in a temporary work table, or inlined into the outer query plan. SQL Server similarly inlines CTEs unless they appear in recursive contexts.

⚠

Common Pitfall

Do not write WITH cte_1 AS (…) WITH cte_2 AS (…). A second WITH keyword is a syntax error. All CTE definitions must follow a single WITH, separated by commas.

Column Aliasing in CTEs

You may optionally specify column names in the CTE header: cte_name (col1, col2) AS (SELECT …). This is especially useful when the inner SELECT uses expressions or aggregates that lack intuitive names. If you omit the column list, the CTE inherits column names from its SELECT clause, so aliasing in the SELECT itself serves the same purpose.

SECTION 5

Common Multi-CTE Patterns

In practice, certain patterns recur when developers compose multiple CTEs. Understanding these patterns helps you decide how to partition query logic across CTE boundaries.

Four common multi-CTE patterns: Linear Pipeline chains CTEs sequentially; Fan-In joins independent CTEs; Fork reuses one CTE from multiple consumers; Hybrid combines pipeline and fan-in approaches for complex analytical queries.

Pattern	When to Use	Example Scenario
Linear Pipeline	Transformations are strictly sequential; each step depends only on the immediately preceding result.	Filter raw events → aggregate per user → rank users by activity.
Fan-In	Multiple independent data sources or aggregations must be combined in the final output.	Compute revenue CTE and cost CTE separately, then join to calculate profit.
Fork	A single cleaned or filtered dataset must be aggregated in multiple, divergent ways.	Base CTE of valid transactions; one downstream CTE sums by region, another counts by product.
Hybrid	Complex analytics requiring both staged transformations and merging of independent data streams.	Clean raw data in pipeline CTEs, compute a parameters CTE from config, then combine for the final report.

Choosing the right multi-CTE pattern based on data dependency structure

SECTION 6

Worked Example — Identifying Top-Spending Customers

Suppose we have an e-commerce database with tables orders(order_id, customer_id, order_date, amount) and customers(customer_id, name, region). We want to find the top 5 customers by total spending in the last 90 days, along with their region and order count. We will build this query using three CTEs in a linear pipeline.

Top 5 Customers by Recent Spending

1
Step 1 — Define CTE 1: Filter Recent Orders

The first CTE filters the orders table to include only orders placed within the last 90 days and joins with the customers table to pull in the customer name and region. WITH recent_orders AS ( SELECT o.customer_id, c.name, c.region, o.amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days' )

Result: a flat table of (customer_id, name, region, amount) for recent orders only.
2
Step 2 — Define CTE 2: Aggregate Per Customer

The second CTE reads from recent_orders and groups by customer to compute total spending and order count. order_totals AS ( SELECT customer_id, name, region, SUM(amount) AS total_spent, COUNT(*) AS order_count FROM recent_orders GROUP BY customer_id, name, region )

Result: one row per customer with total_spent and order_count.
3
Step 3 — Define CTE 3: Rank Customers

The third CTE applies a window function to rank customers by descending total spending. ranked AS ( SELECT *, RANK() OVER (ORDER BY total_spent DESC) AS spending_rank FROM order_totals )

Result: each row now includes a spending_rank column.
4
Step 4 — Write the Final SELECT

The outer SELECT filters for only the top 5 ranked customers and formats the output. SELECT name, region, total_spent, order_count, spending_rank FROM ranked WHERE spending_rank <= 5 ORDER BY spending_rank;

Final output: 5 rows showing the highest-spending customers with their region, total spent, order count, and rank.
5
Step 5 — Complete Assembled Query

Putting it all together as a single, readable statement: WITH recent_orders AS ( SELECT o.customer_id, c.name, c.region, o.amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days' ), order_totals AS ( SELECT customer_id, name, region, SUM(amount) AS total_spent, COUNT(*) AS order_count FROM recent_orders GROUP BY customer_id, name, region ), ranked AS ( SELECT *, RANK() OVER (ORDER BY total_spent DESC) AS spending_rank FROM order_totals ) SELECT name, region, total_spent, order_count, spending_rank FROM ranked WHERE spending_rank <= 5 ORDER BY spending_rank;

Each CTE handles exactly one concern: scoping, aggregation, and ranking. The result is a query that is easy to debug—you can run each CTE independently by replacing the final SELECT.

SECTION 7

Multiple CTEs vs. Alternatives

Multiple CTEs are not the only way to decompose complex queries. Nested subqueries, temporary tables, views, and even application-level code can achieve similar results. The table below compares multiple CTEs against these alternatives on several dimensions that matter in practice.

Criterion	Multiple CTEs	Nested Subqueries	Temp Tables
Readability	Excellent — named stages read top-to-bottom.	Poor — deeply nested, read inside-out.	Good — named, but scattered across statements.
Scope	Single statement only.	Single statement only.	Session-level; persists until dropped or session ends.
Reusability within query	Can be referenced multiple times in the same statement.	Must duplicate subquery text if referenced more than once.	Can be referenced in multiple statements.
Optimization	Optimizer may inline; full predicate pushdown possible.	Optimizer sees the full query tree directly.	Forces materialization; optimizer sees each statement independently.
Debugging	Easy — run each CTE as a standalone SELECT.	Hard — must extract inner subqueries manually.	Easy — SELECT * FROM temp_table at any point.
Side effects	None — pure, inline computation.	None — pure, inline computation.	Creates objects; requires cleanup. May cause locking in some engines.

Comparison of multiple CTEs with nested subqueries and temporary tables

✦

KEY TAKEAWAY

Multiple CTEs strike the best balance between readability and performance for single-statement analytical queries. Use temporary tables when intermediate results must persist across statements or when forced materialization provides a performance benefit on very large datasets. Avoid nested subqueries beyond two levels of depth—they are almost always better expressed as CTEs.

SECTION 8

Connection to Advanced CTE Features

Once you are comfortable composing multiple non-recursive CTEs, the natural next step is to explore recursive CTEs and engine-specific materialization hints. Recursive CTEs use the same WITH syntax but allow a CTE to reference itself, enabling traversal of hierarchical data (organizational charts, bill-of-materials, graph adjacency lists). You can freely mix recursive and non-recursive CTEs in a single WITH block; simply add the RECURSIVE keyword after WITH (in PostgreSQL) or mark individual CTEs as recursive (in SQL Server via the anchor/recursive member pattern).

Feature	Multiple (Non-Recursive) CTEs	Recursive CTEs
Self-reference	Not allowed—each CTE may only reference previously defined CTEs.	Required—the CTE references its own name in a UNION ALL.
Use case	Staged transformations, multi-source joins, ranked analytics.	Hierarchical queries, graph traversal, series generation.
Termination	Always terminates—no iteration.	Must guarantee termination via a base case and convergence condition.
Keyword	`WITH cte AS (…)`	`WITH RECURSIVE cte AS (…)`
Mixing	Can appear in a WITH RECURSIVE block alongside recursive CTEs.	Can appear alongside non-recursive CTEs.

Non-recursive multiple CTEs vs. recursive CTEs

Modern analytics engineering frameworks like dbt (data build tool) take the multi-CTE philosophy further by encouraging developers to write each transformation as a separate model (SQL file), which dbt then compiles into a DAG of materialized views or tables. Understanding multi-CTE composition at the query level is the conceptual foundation for this model-level decomposition in production data pipelines.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why a single WITH keyword is used for multiple CTEs instead of separate WITH keywords for each CTE. What would happen if you wrote WITH a AS (…) WITH b AS (…) SELECT …?

Show answer

The SQL standard defines WITH as a clause that introduces a list of CTEs for a single statement. Writing a second WITH keyword is a syntax error because the parser interprets it as the beginning of a new statement (or an unexpected token). All CTEs must be declared after a single WITH, separated by commas, so that the engine can resolve forward-reference visibility—each CTE can see all CTEs defined above it in the comma-separated list. This design enforces a clear dependency order and keeps CTE scope tied to exactly one SQL statement.

Question 2: BASIC

Given a table sales(sale_id, product_id, sale_date, quantity, price), write a query using two CTEs: the first computes total revenue (quantity × price) per product, and the second filters to products with total revenue exceeding 10,000. The final SELECT should return product_id and total_revenue, ordered by total_revenue descending.

Show answer

WITH product_revenue AS (
  SELECT product_id,
         SUM(quantity * price) AS total_revenue
  FROM   sales
  GROUP BY product_id
),
high_revenue AS (
  SELECT product_id, total_revenue
  FROM   product_revenue
  WHERE  total_revenue > 10000
)
SELECT product_id, total_revenue
FROM   high_revenue
ORDER BY total_revenue DESC;

The first CTE (product_revenue) aggregates quantity × price per product. The second CTE (high_revenue) applies the threshold filter. The final SELECT simply orders the filtered results. While the second CTE could be merged into the final SELECT's WHERE clause, separating it demonstrates the principle of isolating filter logic into its own named stage.

Question 3: INTERMEDIATE

You have tables employees(emp_id, name, dept_id, salary) and departments(dept_id, dept_name). Write a query with three CTEs that: (1) computes the average salary per department, (2) identifies departments whose average salary exceeds the company-wide average, and (3) lists employees in those above-average departments along with their department name and how much their salary exceeds the department average.

Show answer

WITH dept_avg AS (
  SELECT dept_id,
         AVG(salary) AS avg_salary
  FROM   employees
  GROUP BY dept_id
),
above_avg_depts AS (
  SELECT dept_id, avg_salary
  FROM   dept_avg
  WHERE  avg_salary > (SELECT AVG(salary) FROM employees)
),
detailed AS (
  SELECT e.name,
         d.dept_name,
         e.salary,
         a.avg_salary AS dept_avg_salary,
         e.salary - a.avg_salary AS above_dept_avg
  FROM   employees e
  JOIN   above_avg_depts a ON e.dept_id = a.dept_id
  JOIN   departments d    ON e.dept_id = d.dept_id
)
SELECT name, dept_name, salary, dept_avg_salary,
       ROUND(above_dept_avg, 2) AS salary_diff
FROM   detailed
ORDER BY salary_diff DESC;

CTE 1 computes per-department averages. CTE 2 uses a scalar subquery to find the company-wide average and filters to above-average departments. CTE 3 joins employees back to the filtered departments, enriching each row with the department name and salary difference. The final SELECT simply formats and orders the output.

Question 4: APPLIED

A web analytics database has tables page_views(user_id, page, view_time) and conversions(user_id, conversion_time, revenue). Using multiple CTEs (fan-in pattern), write a query that computes: (a) the number of page views per user in the last 30 days, (b) the total conversion revenue per user in the last 30 days, and then joins these to produce a report showing user_id, view_count, total_revenue, and revenue_per_view. Include only users who have both page views and conversions.

Show answer

WITH user_views AS (
  SELECT user_id,
         COUNT(*) AS view_count
  FROM   page_views
  WHERE  view_time >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
),
user_conversions AS (
  SELECT user_id,
         SUM(revenue) AS total_revenue
  FROM   conversions
  WHERE  conversion_time >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT v.user_id,
       v.view_count,
       c.total_revenue,
       ROUND(c.total_revenue / v.view_count, 2) AS revenue_per_view
FROM   user_views v
JOIN   user_conversions c ON v.user_id = c.user_id
ORDER BY revenue_per_view DESC;

This uses the fan-in pattern: two independent CTEs each aggregate a different data source by user_id, then the final SELECT performs an inner JOIN to combine them. The inner join naturally filters to users with both views and conversions. The division in revenue_per_view is safe because view_count is always ≥ 1 for any user in user_views.

Question 5: CRITICAL THINKING

Consider a query with four CTEs where CTE_D references CTE_A, CTE_B, and CTE_C, but CTE_B and CTE_C are completely independent of each other (and both reference only CTE_A). A colleague proposes replacing the four CTEs with three temporary tables and a final SELECT, arguing that "materializing intermediate results is always faster because it avoids redundant computation." Critique this claim. Under what circumstances could the CTE version actually outperform the temp table version? Under what circumstances would the temp tables win?

Show answer

The colleague's claim is incorrect as a blanket statement. Modern optimizers inline non-recursive CTEs and can apply global optimizations—predicate pushdown, join reordering, and column pruning—across CTE boundaries, potentially producing a more efficient overall plan than three separate temp-table materializations. If CTE_A produces a small result and CTE_B and CTE_C apply selective filters, the optimizer may push those filters into the base table scan, reading far less data than a full materialization of CTE_A would require. Furthermore, temp tables incur write I/O, catalog overhead, and in some engines (e.g., older MySQL), create implicit commits that affect transactional behavior. However, temp tables can outperform CTEs when CTE_A is expensive and referenced multiple times (by both CTE_B and CTE_C). If the optimizer inlines CTE_A into both consumers, the expensive computation runs twice. Materializing CTE_A into a temp table ensures it executes once. PostgreSQL addresses this with the MATERIALIZED hint, which forces a CTE to be computed once without requiring a temp table. The optimal choice depends on the engine's optimizer behavior, data volumes, and the cost profile of each stage—there is no universally correct answer.

SUMMARY

Summary — Multiple CTEs for Modular Query Design

Summary

Multiple CTEs enable you to decompose a complex SQL query into a sequence of named, self-documenting stages, each performing a single logical transformation. All CTEs share a single WITH keyword and are separated by commas. The key visibility rule is sequential: each CTE can reference only CTEs declared before it. This top-down ordering mirrors the flow of data through a pipeline and makes the query's intent transparent to reviewers.

The four primary composition patterns—linear pipeline, fan-in, fork, and hybrid—cover the vast majority of real-world analytical queries. Compared to nested subqueries, multiple CTEs offer superior readability and debuggability. Compared to temporary tables, they avoid side effects and allow the optimizer to apply global optimizations. Mastering multi-CTE composition is the foundation for recursive CTEs and modern analytics engineering practices.