Course contentsShow
← Data Science
Lesson 992 of 2,14525. SQL Subqueries and CTEsFree lesson

Multiple CTEs in a Single Query

Defining multiple CTEs separated by commas to break complex queries into logical, manageable steps.

Multiple CTEs in a Single Query

What you'll learn: How to chain multiple CTEs together in one query to break down complex logic into clear, readable steps.

The Core Idea

Just as you can define one CTE before your main query, you can define multiple CTEs by separating them with commas. Each CTE can reference the ones defined before it, allowing you to build up your analysis in logical layers. Think of it like a recipe with multiple prep steps—first you chop vegetables, then you make the sauce, then you combine everything. Each step builds on the previous ones.

Basic Syntax Pattern

WITH
  cte1 AS (
    SELECT column1, column2
    FROM table1
    WHERE condition
  ),
  cte2 AS (
    SELECT column1, COUNT(*) as count
    FROM cte1  -- Can reference cte1!
    GROUP BY column1
  ),
  cte3 AS (
    SELECT *
    FROM cte2
    WHERE count > 10
  )
SELECT *
FROM cte3;

Notice:

  • Single WITH keyword at the start
  • Each CTE separated by a comma (not another WITH)
  • Later CTEs can reference earlier ones
  • Only the final SELECT ends with a semicolon

Why Use Multiple CTEs?

Instead of nesting subqueries deeply or writing one massive query, multiple CTEs let you:

  • Break complex logic into named, understandable pieces
  • Build calculations step-by-step
  • Make debugging easier (you can test each CTE independently)
  • Improve code readability for yourself and teammates

Example Mental Model

Think of it like variables in programming: you calculate total_sales, then use that to calculate sales_growth, then filter to high_growth_products—each step clearly labeled and building on the last.

Key Takeaway: Multiple CTEs transform complex queries into a readable pipeline of logical steps, with each CTE building on previous ones using comma-separated definitions after a single WITH keyword.