SQL CTE (WITH Clause): Syntax, Chaining, Recursive CTEs & Real Examples (2026)

Here is a query that would embarrass any data analyst in a code review: an aggregate subquery wrapped inside a join, wrapped inside another subquery, parentheses three layers deep. To understand what it calculates, you must start in the deepest inner block and trace your way outward, juggling temporary aliases in your head.

CTEs (Common Table Expressions) solve this architectural problem completely. Defined with the WITH keyword, CTEs allow you to write SQL queries that read linearly from top to bottom, like a recipe. You give each intermediate step an intuitive name, stack them sequentially, and query the final output cleanly.

This comprehensive guide covers standard CTE syntax, execution lifecycle and memory scoping, multi-CTE chaining, recursive CTEs for hierarchical tree traversal, and the architectural differences between CTEs, subqueries, and temporary tables. Every code snippet is verified against a production PostgreSQL database containing real e-commerce and organizational records.

1. What is a CTE in SQL? Syntax & Anatomy

A Common Table Expression (CTE) is a temporary, named result set that you define at the beginning of a SQL statement. Once declared with the WITH keyword, you can reference this named result anywhere in the primary query just like a regular database table or view.

Let's define a basic CTE named monthly_revenue that groups completed orders by month, and then select the result:

WITH monthly_revenue AS (
    SELECT DATE_TRUNC('month', order_date)::date AS month,
           ROUND(SUM(total_amount), 2)           AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY month
)
SELECT month, revenue
FROM monthly_revenue
ORDER BY month;

The Result

Everything inside the parentheses represents step one, and it is given the explicit name monthly_revenue. Below the WITH block, the main SELECT query treats monthly_revenue exactly like a table on disk.

The output is identical to a standard GROUP BY, but the intermediate logic has a distinct label. The final query reads naturally: "Given the monthly revenue, show me the records sorted chronologically."

Syntax Anatomy Breakdown

WITH cte_name AS (
    -- Step 1: Define the temporary intermediate logic
    SELECT column_1, column_2
    FROM source_table
    WHERE conditions
)
-- Step 2: Query the CTE in the main consuming statement
SELECT *
FROM cte_name;

2. The CTE Execution Lifecycle: Scoping & Storage

A frequent interview question is: "Where is a CTE stored, and how long does it live?"

A CTE is strictly scoped to the single SQL statement in which it is defined. It is not an object saved in the database catalog, and it is not written to permanent storage.

  +-------------------------------------------------------------------------+
  |                   SQL CTE EXECUTION LIFECYCLE                           |
  +-------------------------------------------------------------------------+
  |                                                                         |
  |  1. QUERY COMPILATION & PARSING                                         |
  |     WITH monthly_revenue AS (                                           |
  |       SELECT DATE_TRUNC('month', order_date)::date AS month, ...        |
  |     )                                                                   |
  |     * Query planner verifies syntax and resolves table permissions      |
  |     * Optimizer determines execution plan (inlining vs. materialization)|
  |     * Allocates transient memory buffer (work_mem / tempdb)             |
  |                                                                         |
  |                                |                                        |
  |                                v                                        |
  |  2. STATEMENT EXECUTION (Active Scope Window)                           |
  |     SELECT month, revenue FROM monthly_revenue WHERE revenue > 20000;   |
  |     +-------------------------------------------------------------+     |
  |     | CTE SCOPE: Active ONLY for this single consuming statement  |     |
  |     | - Can be read multiple times within the same statement      |     |
  |     | - Can join with subsequent CTEs or physical base tables     |     |
  |     +-------------------------------------------------------------+     |
  |                                                                         |
  |                                | (Query Finishes / Semicolon Reached ;) |
  |                                v                                        |
  |  3. AUTOMATIC SCOPE TERMINATION                                         |
  |     SELECT * FROM monthly_revenue;                                      |
  |     --> ERROR: relation "monthly_revenue" does not exist                |
  |     * Transient memory buffer immediately deallocated                   |
  |     * Zero disk cleanup or DROP TABLE commands required                 |
  |                                                                         |
  +-------------------------------------------------------------------------+

Demonstrating the Scope Boundary

Notice what happens if you attempt to reference a CTE in a subsequent statement:

-- Statement 1: Runs successfully
WITH monthly_revenue AS (
    SELECT DATE_TRUNC('month', order_date)::date AS month,
           ROUND(SUM(total_amount), 2)           AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY month
)
SELECT * FROM monthly_revenue ORDER BY month;
 
-- Statement 2: Fails immediately
SELECT * FROM monthly_revenue;
-- ERROR: relation "monthly_revenue" does not exist (SQLSTATE 42P01)

Because the CTE vanishes the microsecond Statement 1 finishes, it introduces zero clutter into your database schema, eliminates concurrency locking across user sessions, and requires no maintenance scripts or DROP TABLE cleanup routines.

3. Filtering Intermediate Results: CTE vs HAVING

Because the CTE acts like a table, you can filter it. Which months cleared a twenty-thousand-rupee bar?

WITH monthly_revenue AS (
    SELECT DATE_TRUNC('month', order_date)::date AS month,
           ROUND(SUM(total_amount), 2)           AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY month
)
SELECT month, revenue
FROM monthly_revenue
WHERE revenue > 20000
ORDER BY revenue DESC;

The standout months float to the top, sorted biggest-first.

Why This Is Better Than HAVING

You could write this with HAVING:

SELECT DATE_TRUNC('month', order_date)::date AS month,
       ROUND(SUM(total_amount), 2)           AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY month
HAVING SUM(total_amount) > 20000
ORDER BY revenue DESC;

This works for a single filter. But the moment the logic gets layered — filter, then compare to an average, then rank — HAVING forces you back into nested subqueries. The CTE gives you a clean surface to build the next step on.

4. Chaining Multiple CTEs in a Single Query

The greatest architectural strength of CTEs is chaining: defining multiple named steps separated by commas under a single WITH clause, where later steps can reference earlier ones.

Suppose your stakeholders ask: "Which months exceeded the average monthly revenue across the entire history of the store?"

To solve this:

1. Step 1 (monthly_revenue): Calculate the total completed revenue for each month.
2. Step 2 (overall): Calculate the overall average across all individual months produced in Step 1.
3. Step 3 (Main Query): Filter the monthly records against the benchmark.

WITH monthly_revenue AS (
    -- Step 1: Calculate revenue per month
    SELECT DATE_TRUNC('month', order_date)::date AS month,
           SUM(total_amount)                     AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY month
),
overall AS (
    -- Step 2: Calculate benchmark average from Step 1
    SELECT AVG(revenue) AS avg_revenue
    FROM monthly_revenue
)
-- Step 3: Compare each month against the overall average
SELECT m.month,
       ROUND(m.revenue, 2)                          AS revenue,
       ROUND((SELECT avg_revenue FROM overall), 2) AS avg_revenue
FROM monthly_revenue AS m
WHERE m.revenue > (SELECT avg_revenue FROM overall)
ORDER BY m.month;

How It Reads: Linear vs Nested Execution

Notice the difference when you read this query top to bottom:

1. monthly_revenue — aggregates completed orders by month.
2. overall — computes the single average revenue metric across those months.
3. Main SELECT — isolates months above that average, displaying actual revenue alongside the benchmark.

Step 2 queries Step 1 — that is how CTEs chain. To compare each month against the overall average, the main query references the scalar value cleanly.

The Unreadable Nested Alternative

Consider how this exact same logic looks without CTEs:

-- The messy, deeply nested subquery equivalent:
SELECT month, revenue
FROM (
    SELECT month, revenue,
           (SELECT AVG(revenue) FROM (
               SELECT SUM(total_amount) AS revenue
               FROM orders
               WHERE status = 'completed'
               GROUP BY DATE_TRUNC('month', order_date)
           ) sub
           ) AS avg_revenue
    FROM (
        SELECT DATE_TRUNC('month', order_date)::date AS month,
               SUM(total_amount) AS revenue
        FROM orders
        WHERE status = 'completed'
        GROUP BY month
    ) inner_query
) outer_query
WHERE revenue > avg_revenue;

Parentheses three layers deep, aliases nested inside aliases, and logic evaluated from the inside out. Chaining CTEs eliminates this cognitive friction entirely.

5. Recursive CTEs: Traversing Hierarchical Data & Org Charts

Standard CTEs handle linear, sequential transformations. But what happens when your data has a parent-child hierarchy where the depth of the tree is unknown?

Common examples in analytics engineering include:

• Employee reporting hierarchies (organizational charts)
• E-commerce product taxonomy trees (Category → Subcategory → Item)
• Bill of materials (assemblies and sub-assemblies)
• Dynamic date range generation without physical calendar tables

To solve these problems without writing procedural loops, SQL provides the Recursive CTE (WITH RECURSIVE).

The 3 Core Components of Recursion

A recursive CTE consists of three mandatory elements joined by UNION ALL:

1. Anchor Member: The non-recursive baseline query that seeds the recursion (e.g., finding the top-level CEO who has no manager).
2. UNION ALL Operator: Glues the anchor result to subsequent recursive iterations.
3. Recursive Member: A query that references the CTE itself, joining child records to parent records until no further relationships exist.

WITH RECURSIVE cte_name AS (
    -- 1. Anchor Member (Base Case)
    SELECT id, parent_id, name, 1 AS level
    FROM hierarchy_table
    WHERE parent_id IS NULL
 
    UNION ALL
 
    -- 2. Recursive Member (Iterative Case)
    SELECT child.id, child.parent_id, child.name, parent.level + 1
    FROM hierarchy_table AS child
    JOIN cte_name AS parent ON child.parent_id = parent.id
)
SELECT * FROM cte_name;

Real-World Example: Tech Startup Org Chart

Consider an employees table at a high-growth technology company. Each employee has an emp_id, name, title, and a manager_id referencing their manager's emp_id. The CEO has manager_id = NULL.

Sample Table: employees

The Recursive Hierarchy Query

We want to generate a complete organizational chart showing each employee's management tier (level) and their full reporting path from the CEO down to individual contributors:

WITH RECURSIVE org_hierarchy AS (
    -- Anchor Member: Select top-level leadership (CEO)
    SELECT emp_id,
           name,
           title,
           manager_id,
           1 AS level,
           name::text AS reporting_path
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    -- Recursive Member: Join direct reports to their managers
    SELECT e.emp_id,
           e.name,
           e.title,
           e.manager_id,
           h.level + 1 AS level,
           (h.reporting_path || ' -> ' || e.name)::text AS reporting_path
    FROM employees AS e
    JOIN org_hierarchy AS h ON e.manager_id = h.emp_id
)
SELECT emp_id,
       name,
       title,
       level,
       reporting_path
FROM org_hierarchy
ORDER BY level, emp_id;

The Result

Step-by-Step Recursion Execution Loop

How does the SQL database engine execute this recursive CTE?

Behind the scenes, the database query processor manages two temporary internal tables: a working table and an intermediate accumulator:

6. CTE vs Subquery vs Temporary Table: Architectural Comparison

Choosing between an inline subquery, a Common Table Expression, and a physical temporary table is one of the most common architecture choices in data engineering.

The Decision Framework: Which Tool Should You Pick?

1. Use an Inline Subquery for simple, single-level scalar lookups (e.g., WHERE salary > (SELECT AVG(salary) FROM employees)).
2. Use a CTE when your query nests two or more levels deep, requires self-documenting step names, uses recursive hierarchy traversal, or references the same summarized result multiple times.
3. Use a Temporary Table (CREATE TEMPORARY TABLE) when processing millions of rows across multiple distinct queries in an ETL batch job, where building explicit indexes (CREATE INDEX ON #temp (id)) will accelerate downstream joins.

7. Readability Is Not Correctness: The JOIN Fan-Out Trap

A dangerous pitfall among analysts is assuming that because a CTE looks clean and well-structured, its output must be numerically correct.

A tidy CTE can still silently multiply your numbers.

Consider an e-commerce database where an analyst attempts to calculate total company revenue by summing orders.total_amount. They write the following clean-looking CTE:

-- Readable but dangerously WRONG — this CTE has a join fan-out bug:
WITH order_totals AS (
    SELECT o.order_id,
           o.total_amount
    FROM orders AS o
    JOIN order_items AS oi ON o.order_id = oi.order_id  -- ← Fan-out occurs here!
)
SELECT ROUND(SUM(total_amount), 2) AS total_revenue
FROM order_totals;

The Bug Explained

Because an order often contains multiple line items in order_items, the JOIN duplicates each order row by the number of items it contains. An order worth ₹1,000 with 4 items gets summed 4 times (₹4,000).

• Calculated Total with Bug: ₹20,09,577.61
• Actual Verified Revenue: ₹7,82,905.04

The CTE produced a beautifully formatted query that is overstated by more than ₹1.2 million.

8. Production-Grade CTE Patterns

Pattern 1: Deduplication with ROW_NUMBER()

Because SQL execution order evaluates WHERE before window functions, you cannot place ROW_NUMBER() directly in a WHERE clause. Wrapping the window function in a CTE is the standard industry pattern:

WITH ranked_reviews AS (
    SELECT review_id,
           customer_id,
           product_id,
           rating,
           review_date,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id, product_id
               ORDER BY review_date DESC, review_id DESC
           ) AS rn
    FROM reviews
)
SELECT review_id,
       customer_id,
       product_id,
       rating,
       review_date
FROM ranked_reviews
WHERE rn = 1;

This pattern reliably extracts the single latest review per customer-product pair. Read our SQL Window Functions Guide and ROW_NUMBER vs RANK vs DENSE_RANK Guide for full syntax details.

Pattern 2: Two-Stage Aggregation Pipeline

When calculating customer lifetime value alongside average order frequency, aggregating across multiple grains simultaneously causes duplication. A chained CTE isolates each grain:

WITH customer_order_metrics AS (
    -- Step 1: Aggregate at the customer grain
    SELECT customer_id,
           COUNT(order_id)         AS total_orders,
           SUM(total_amount)       AS lifetime_spend,
           MIN(order_date)::date   AS first_order_date,
           MAX(order_date)::date   AS last_order_date
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
segmented_customers AS (
    -- Step 2: Apply business segmentation logic
    SELECT customer_id,
           total_orders,
           ROUND(lifetime_spend, 2) AS lifetime_spend,
           CASE 
               WHEN lifetime_spend >= 50000 THEN 'VIP Tier'
               WHEN lifetime_spend >= 15000 THEN 'Growth Tier'
               ELSE 'Standard Tier'
           END AS customer_segment
    FROM customer_order_metrics
)
SELECT customer_segment,
       COUNT(*) AS total_customers,
       ROUND(AVG(lifetime_spend), 2) AS avg_segment_spend
FROM segmented_customers
GROUP BY customer_segment
ORDER BY avg_segment_spend DESC;

Each stage has a clear grain, making business logic easy to audit, modify, and hand over to team members.

Pattern 3: Dynamic Benchmark Comparison via CROSS JOIN

To compare each row's performance against overall summary metrics without executing repetitive subqueries for every row, pair a CTE with a CROSS JOIN:

WITH monthly_metrics AS (
    SELECT DATE_TRUNC('month', order_date)::date AS month,
           ROUND(SUM(total_amount), 2)           AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY month
),
benchmarks AS (
    SELECT ROUND(AVG(revenue), 2) AS avg_monthly_revenue,
           ROUND(MAX(revenue), 2) AS max_monthly_revenue
    FROM monthly_metrics
)
SELECT m.month,
       m.revenue,
       b.avg_monthly_revenue,
       ROUND(m.revenue - b.avg_monthly_revenue, 2) AS variance_from_avg,
       ROUND((m.revenue / b.max_monthly_revenue) * 100, 1) AS pct_of_peak
FROM monthly_metrics AS m
CROSS JOIN benchmarks AS b
ORDER BY m.month;

Because benchmarks produces exactly one row, the CROSS JOIN cleanly appends benchmark columns to all monthly rows without causing row inflation.

Pattern 4: Modifying Data with CTEs (INSERT / UPDATE / DELETE)

In PostgreSQL and modern SQL dialects, CTEs can prepend write operations. Using the RETURNING clause within a CTE allows you to execute atomic data archiving in a single statement:

-- Move archived orders to a historical archive table atomically:
WITH moved_orders AS (
    DELETE FROM active_orders
    WHERE order_date < '2023-01-01'
    RETURNING order_id, customer_id, total_amount, order_date
)
INSERT INTO archived_orders (order_id, customer_id, total_amount, order_date)
SELECT order_id, customer_id, total_amount, order_date
FROM moved_orders;

9. Quick Reference: CTE Cheatsheet

10. Master CTEs with Hands-On Practice

Memorizing SQL syntax is never enough to pass technical interviews or design production-grade data models. Fluency comes from writing queries, debugging execution plans, and refactoring nested logic under timed conditions.