Pathubs SQL Curriculum • Module 13

SQL Multiple CTEs (Chained Pipelines)

Master multi-step data analysis in SQL: chain sequential Common Table Expressions using comma separation, build forward dependency pipelines, isolate intermediate calculations, and debug complex queries step-by-step.

⏱️ Estimated Time:30 Minutes

🎯 Level:Intermediate Analytical SQL

📊 Track:Data Analytics & Pipeline Engineering

✨ Mode:Interactive Pipeline Lab

Introduction: Multi-Stage Analytical Problems

Real-world business metrics rarely come from a single raw table query. Consider a customer lifetime value report:

Raw Orders ➔ Calculate Customer Spending ➔ Classify High-Value Segments ➔ Final Executive Report

Packing all these stages into one monolithic nested query creates unreadable SQL. Multiple CTEs allow you to decompose this problem into clean, sequential, named steps.

Basic Multiple CTE Syntax & Rules

To define multiple CTEs in a single query, write the WITH keyword only once at the top, separating each CTE definition with a comma:

WITH cte_one AS (
    -- Step 1: Prepares raw data
    SELECT ...
),
cte_two AS (
    -- Step 2: Queries cte_one!
    SELECT ... FROM cte_one
)
-- Final Query: Presents the result
SELECT * FROM cte_two;

Diagram 1: Multi-Stage CTE Execution Flow

Step 1 (CTE 1)
cte_one

── Passes Result ──▶

Step 2 (CTE 2)
cte_two

── Final SELECT ──▶

Output
Final Result Set

First Practical Example: Two-Step Department Rollup

Calculating department average salaries in Step 1, then filtering high-compensation departments in Step 2:

WITH department_salary AS (
    -- Step 1: Calculate department averages
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
high_salary_departments AS (
    -- Step 2: Filter departments from Step 1
    SELECT *
    FROM department_salary
    WHERE avg_salary > 60000
)
SELECT *
FROM high_salary_departments;

Understanding CTE Dependency & Forward Order

CTEs evaluate in forward sequential order. A later CTE can reference any earlier CTE defined before it in the same WITH statement:

Linear Forward Dependency Rule

CTE 2 can query CTE 1.
CTE 3 can query CTE 2 and CTE 1.
CTE 1 CANNOT query CTE 2(backward references cause "relation does not exist" errors).

Diagram 2: Linear Forward Dependency Graph

CTE 1 (Root)
Queries physical tables

CTE 2 (Dependent)
Queries CTE 1

CTE 3 (Aggregator)
Queries CTE 2 & CTE 1

Multiple CTEs With Different Tables

You can use multiple CTEs to independently prepare subsets from different tables before combining them in your final query:

WITH
active_customers AS (
SELECT id, name, city FROM customers WHERE city = 'Mumbai'
),
high_orders AS (
SELECT customer_id, amount FROM orders WHERE amount > 3000
)
SELECT c.name, o.amount
FROM active_customers AS c
INNER JOIN high_orders AS o ON c.id = o.customer_id;

Multiple CTEs With JOIN Operations

Aggregating transactional orders in CTE 1, then joining customer profile data in CTE 2:

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
customer_details AS (
    SELECT c.id, c.name, ct.total_spent
    FROM customers AS c
    INNER JOIN customer_totals AS ct ON c.id = ct.customer_id
)
SELECT *
FROM customer_details
WHERE total_spent > 5000;

Multiple CTEs With CASE Classification

Stage 1 calculates financial totals, Stage 2 segments users into tiers, and Stage 3 filters high-tier users:

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
customer_segments AS (
    SELECT
        customer_id,
        total_spent,
        CASE
            WHEN total_spent >= 10000 THEN 'High'
            WHEN total_spent >= 5000 THEN 'Medium'
            ELSE 'Low'
        END AS segment
    FROM customer_totals
)
SELECT *
FROM customer_segments
WHERE segment = 'High';

Three-Step Data Analytics Showcase: Above-Average Sales Months

A complete analytics pipeline finding which calendar months beat the company's monthly average:

WITH
-- Step 1: Calculate total sales per month
monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS sales_month,
        SUM(amount) AS monthly_revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
),
-- Step 2: Calculate average monthly revenue benchmark
avg_benchmark AS (
    SELECT AVG(monthly_revenue) AS benchmark_revenue
    FROM monthly_sales
),
-- Step 3: Filter months beating the benchmark
above_avg_months AS (
    SELECT m.sales_month, m.monthly_revenue, b.benchmark_revenue
    FROM monthly_sales AS m
    CROSS JOIN avg_benchmark AS b
    WHERE m.monthly_revenue > b.benchmark_revenue
)
SELECT * FROM above_avg_months;

Multiple CTEs vs. One Giant Monolithic Query

Attribute	Multiple Chained CTEs	Giant Nested Query
Reading Direction	Linear Top-to-Bottom	Inside-Out (from deepest parenthesis)
Debugging Simplicity	Easy (Inspect each CTE individually)	Hard (Must unravel entire nested structure)
Modularity	Reusable named datasets within the statement	Redundant subquery copy-pasting

Multiple CTEs vs. Multiple Subqueries

While both produce identical execution plans in modern database engines, Multiple CTEs give intermediate datasets clear semantic names rather than anonymous inline subquery aliases.

CTE Naming Best Practices

✅ Descriptive Domain Names:

monthly_sales
customer_totals
high_value_customers

❌ Vague Meaningless Names:

temp1, t1
data_step_a
x

Common Multiple CTE Mistakes to Avoid

1. Trailing Comma Before Final SELECT

Leaving a comma after the last CTE closing parenthesis causes a fatal SQL syntax error.

2. Repeating the 'WITH' Keyword

Writing WITH cte1 AS (...), WITH cte2 AS (...). Write WITH once only.

3. Backward References

Attempting to reference a CTE that has not yet been declared earlier in the sequence.

4. Forgetting the Final Main Query

Ending the statement right after the CTE closing parenthesis without a final SELECT.

Step-by-Step CTE Debugging Technique

When a multi-step query returns incorrect numbers, test each CTE independently by temporarily changing the final SELECT:

-- Step 1 Debug: Inspect CTE 1
SELECT * FROM customer_totals;

-- Step 2 Debug: Inspect CTE 2
SELECT * FROM customer_segments;

Diagram 3: Step-by-Step Intermediate Debugging

Debug Step 1
SELECT * FROM cte_1

── Verified ──▶

Debug Step 2
SELECT * FROM cte_2

── Verified ──▶

Full Query
SELECT * FROM final_cte

Practical Multi-Step Exercises

Exercise 1: Customer Order Frequency & High Spender Filter

WITH order_metrics AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
vip_customers AS (
    SELECT c.name, om.order_count, om.total_spent
    FROM customers AS c
    INNER JOIN order_metrics AS om ON c.id = om.customer_id
    WHERE om.total_spent > 5000
)
SELECT * FROM vip_customers
ORDER BY total_spent DESC;

⚡ Live Interactive Multiple CTE Practice Lab

Step through multi-stage data pipelines, inspect intermediate datasets, and test multi-step sales analytics

Step 1: Intermediate CTE Definition

WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total_spent, COUNT(*) AS orders_count
FROM orders GROUP BY customer_id
)
-- Debug Output: SELECT * FROM customer_totals;

Customer ID	Name	City	Order Count	Total Spent
101	Rahul Sharma	Mumbai	2 order(s)	$8,300
102	Amit Verma	Delhi	0 order(s)	$0
103	Priya Patel	Bengaluru	2 order(s)	$15,300
104	Neha Singh	Pune	1 order(s)	$2,400
105	Rohan Gupta	Hyderabad	1 order(s)	$5,200

What You Should Know Now: Checklist

✓Multiple CTE Syntax: Single WITH keyword, comma-separated CTE blocks.
✓Forward Dependency: Later CTEs can reference earlier CTEs defined before them.
✓Modular Analytics: Prepare ➔ Aggregate ➔ Classify ➔ Present in clean linear stages.
✓Debugging Strategy: Inspect intermediate CTE outputs by updating the final SELECT.

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL Multiple CTEs

Test your understanding of comma-separated CTE definitions, forward dependencies, debugging intermediate outputs, and multi-step pipeline architectures.

Question 1 of 8Current Score: 0 / 0

How do you separate multiple CTE definitions within a single SQL statement?