Multiple CTEs

Overview

SQL allows you to chain multiple CTEs within a single WITH clause, where subsequent CTEs can reference previously defined CTEs.

Learning Objectives

• Chain multiple CTE declarations using comma separation.
• Build multi-stage data transformation pipelines in pure SQL.
• Combine independent aggregations cleanly before a final join.

Detailed Concept Explanation

To define multiple CTEs, specify the WITH keyword once, and separate each CTE block with a comma:

WITH CTE1 AS (
    SELECT ...
),
CTE2 AS (
    -- Can reference CTE1!
    SELECT ... FROM CTE1
)
SELECT * FROM CTE2;

Code Examples

SQL

-- Pipeline: Step 1 = Staged Sales, Step 2 = Aggregated Monthly Sales, Step 3 = Final Join
WITH CustomerSales AS (
    SELECT customer_id, amount, order_date
    FROM orders
    WHERE order_date >= '2026-01-01'
),
CustomerTotals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM CustomerSales
    GROUP BY customer_id
)
SELECT 
    c.name,
    ct.total_spent
FROM CustomerTotals ct
JOIN customers c ON ct.customer_id = c.customer_id
WHERE ct.total_spent > 1000;

Best Practices

• Single WITH Keyword: Write the WITH keyword only once at the start. Do not repeat WITH for second or third CTEs—just separate them with commas.

Interview Perspective

Interactive Challenges

Summary

Chain multiple CTEs in a single WITH clause separated by commas to construct modular, multi-step SQL pipelines.