Chaining Multiple CTEs in a Single Query0%

Chaining Multiple CTEs in a Single Query

Intermediate13 min read•Updated: 2026-09-12

Chaining Multiple CTEs in a Single Query

One of the most potent capabilities of the WITH clause is the ability to define multiple Common Table Expressions in a single query, separated by commas. Furthermore, subsequent CTEs can directly reference previously defined CTEs within the same WITH block, creating an elegant, pipeline-style data transformation workflow.

Syntax for Chaining Multiple CTEs

Notice that the keyword WITH is specified only once at the very beginning:

1WITH 
  cte_first AS (
      SELECT ...
  ),
  cte_second AS (
      SELECT ...
      FROM cte_first  -- References the first CTE!
      WHERE ...
  ),
  cte_third AS (
      SELECT ...
      FROM cte_second -- References the second CTE!
  )
14-- Final consuming query
15SELECT *
16FROM cte_third;

Practical Example: Multi-Stage E-Commerce Sales Pipeline

Suppose management wants to find the top 5% highest spending customers in each geographic region and compare their spending against the regional average.

Without multiple CTEs, this requires three levels of nested subqueries. With chained CTEs, the logic unfolds step-by-step:

1WITH 
  -- Step 1: Compute total lifetime spend for each customer
  CustomerSpending AS (
      SELECT 
          c.customer_id,
          CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
          c.country,
          SUM(o.total_amount) AS total_spent
      FROM customers c
      JOIN orders o ON c.customer_id = o.customer_id
      WHERE o.order_status = 'completed'
      GROUP BY c.customer_id, c.first_name, c.last_name, c.country
  ),
  
  -- Step 2: Compute country-wide averages referencing CustomerSpending
  CountryBenchmarks AS (
      SELECT 
          country,
          AVG(total_spent) AS avg_country_spend,
          MAX(total_spent) AS max_country_spend
      FROM CustomerSpending
      GROUP BY country
  ),
  
  -- Step 3: Flag high-performing outliers by joining both CTEs
  CustomerOutliers AS (
      SELECT 
          cs.customer_id,
          cs.customer_name,
          cs.country,
          cs.total_spent,
          cb.avg_country_spend,
          ROUND(cs.total_spent / cb.avg_country_spend, 2) AS spend_ratio
      FROM CustomerSpending cs
      JOIN CountryBenchmarks cb ON cs.country = cb.country
      WHERE cs.total_spent > (cb.avg_country_spend * 2.0)
  )
38 
39-- Final consumption
40SELECT 
  customer_name,
  country,
  total_spent,
  avg_country_spend,
  spend_ratio
46FROM CustomerOutliers
47ORDER BY spend_ratio DESC;

Execution Rules & Best Practices

1
Top-Down Dependency: A CTE can reference any CTE defined before it in the same statement. However, a CTE cannot reference a CTE defined after it (forward reference).
2
Naming Conventions: Use clear PascalCase or snake_case names that indicate what data stage the CTE represents (e.g., RawOrders, AggregatedByMonth, FinalAudit).
3
Avoid Over-Materialization: While CTEs improve readability, breaking simple logic into dozens of trivial CTEs can hinder optimization. Keep data transformations purposeful.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

How many times should the WITH keyword appear when chaining multiple CTEs?

Question 2

Can a chained CTE reference a CTE defined earlier in the same WITH clause?

Question 3

Can a CTE reference a CTE that is declared after it in the WITH block?

Question 4

What separates multiple CTE declarations within a single WITH statement?

Question 5

In our multi-stage pipeline example, which CTE consumed data from CustomerSpending?

Next Lesson

Recursive CTEs for Hierarchies & Trees

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Non-Recursive CTEs with WITH Clause	Recursive CTEs for Hierarchies & Trees

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Recursive CTEs for Hierarchies & Trees

Chaining Multiple CTEs in a Single Query

Intermediate13 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Chaining Multiple CTEs in a Single Query

Syntax for Chaining Multiple CTEs

Notice that the keyword WITH is specified only once at the very beginning:

1WITH 
  cte_first AS (
      SELECT ...
  ),
  cte_second AS (
      SELECT ...
      FROM cte_first  -- References the first CTE!
      WHERE ...
  ),
  cte_third AS (
      SELECT ...
      FROM cte_second -- References the second CTE!
  )
14-- Final consuming query
15SELECT *
16FROM cte_third;

Practical Example: Multi-Stage E-Commerce Sales Pipeline

Suppose management wants to find the top 5% highest spending customers in each geographic region and compare their spending against the regional average.

Without multiple CTEs, this requires three levels of nested subqueries. With chained CTEs, the logic unfolds step-by-step:

1WITH 
  -- Step 1: Compute total lifetime spend for each customer
  CustomerSpending AS (
      SELECT 
          c.customer_id,
          CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
          c.country,
          SUM(o.total_amount) AS total_spent
      FROM customers c
      JOIN orders o ON c.customer_id = o.customer_id
      WHERE o.order_status = 'completed'
      GROUP BY c.customer_id, c.first_name, c.last_name, c.country
  ),
  
  -- Step 2: Compute country-wide averages referencing CustomerSpending
  CountryBenchmarks AS (
      SELECT 
          country,
          AVG(total_spent) AS avg_country_spend,
          MAX(total_spent) AS max_country_spend
      FROM CustomerSpending
      GROUP BY country
  ),
  
  -- Step 3: Flag high-performing outliers by joining both CTEs
  CustomerOutliers AS (
      SELECT 
          cs.customer_id,
          cs.customer_name,
          cs.country,
          cs.total_spent,
          cb.avg_country_spend,
          ROUND(cs.total_spent / cb.avg_country_spend, 2) AS spend_ratio
      FROM CustomerSpending cs
      JOIN CountryBenchmarks cb ON cs.country = cb.country
      WHERE cs.total_spent > (cb.avg_country_spend * 2.0)
  )
38 
39-- Final consumption
40SELECT 
  customer_name,
  country,
  total_spent,
  avg_country_spend,
  spend_ratio
46FROM CustomerOutliers
47ORDER BY spend_ratio DESC;

Execution Rules & Best Practices

1
Top-Down Dependency: A CTE can reference any CTE defined before it in the same statement. However, a CTE cannot reference a CTE defined after it (forward reference).
2
Naming Conventions: Use clear PascalCase or snake_case names that indicate what data stage the CTE represents (e.g., RawOrders, AggregatedByMonth, FinalAudit).
3
Avoid Over-Materialization: While CTEs improve readability, breaking simple logic into dozens of trivial CTEs can hinder optimization. Keep data transformations purposeful.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

How many times should the WITH keyword appear when chaining multiple CTEs?

Question 2

Can a chained CTE reference a CTE defined earlier in the same WITH clause?

Question 3

Can a CTE reference a CTE that is declared after it in the WITH block?

Question 4

What separates multiple CTE declarations within a single WITH statement?

Question 5

In our multi-stage pipeline example, which CTE consumed data from CustomerSpending?

Next Lesson

Recursive CTEs for Hierarchies & Trees

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Non-Recursive CTEs with WITH Clause	Recursive CTEs for Hierarchies & Trees

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next