Building with Multiple CTEs

Each CTE builds on the previous ones. You can trace the logic step by step. When something’s wrong, you can run each CTE independently to find the problem.

Syntax for Multiple CTEs

Multiple CTEs are separated by commas. Only one WITH keyword at the beginning:

WITH first_cte AS (
  SELECT ...
),
second_cte AS (
  SELECT ...
  FROM first_cte
  ...
),
third_cte AS (
  SELECT ...
  FROM second_cte
  ...
)
SELECT *
FROM third_cte;

Step-by-Step Analysis Workflow

When facing a complex problem, break it into steps:

• What data do I need first? (first CTE)
• What calculation depends on that? (second CTE)
• What filtering or joining comes next? (third CTE)
• What’s the final output? (main SELECT)

This mirrors how you’d solve the problem on paper. Each CTE is a checkpoint you can verify.

Income By Title and Gender

Use multiple CTEs to organize your aggregations before the final grouping.

Reusing CTEs

Here’s something subqueries can’t do: reference the same result multiple times. CTEs can.

-- CTE reuse: monthly_revenue is defined once but referenced twice
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_order_cost) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  curr.month,
  curr.revenue AS current_month,
  prev.revenue AS previous_month,
  curr.revenue - prev.revenue AS change
FROM monthly_revenue curr
LEFT JOIN monthly_revenue prev
  ON curr.month = prev.month + INTERVAL '1 month'
ORDER BY curr.month;

The monthly_revenue CTE is referenced twice: once for current month, once for previous. Self-joining a CTE is the standard pattern for period-over-period comparisons.

Debugging Chained CTEs

When a multi-CTE query isn’t working, isolate the problem:

-- Debug Step 1: Run just the first CTE
WITH customer_orders AS (
  SELECT
    cust_id,
    SUM(total_order_cost) AS total_spent
  FROM orders
  GROUP BY cust_id
)
SELECT *
FROM customer_orders;  -- Check this output

-- Debug Step 2: Add the second CTE
WITH customer_orders AS (...),
avg_spend AS (
  SELECT AVG(total_spent) AS avg_customer_spend
  FROM customer_orders
)
SELECT *
FROM avg_spend;  -- Check this output

Work through CTEs one at a time until you find where the data goes wrong.

Top Cool Votes

Break down the problem into logical steps using chained CTEs.

Commenting Your CTEs

Add comments to explain each step. Your future self will thank you.

-- Get raw order data with customer info
WITH order_details AS (...),
-- Aggregate to customer level
customer_totals AS (...),
-- Find the threshold (90th percentile)
threshold AS (...),
-- Filter to top customers
top_customers AS (...)
SELECT *
FROM top_customers;

Key Takeaways

• Multiple CTEs are separated by commas after WITH
• Each CTE can reference any CTE defined before it
• The same CTE can be referenced multiple times (unlike subqueries)
• Break complex logic into named, testable steps
• Debug by running CTEs one at a time
• Comment your CTEs to explain each step

What’s Next

You now know how to structure CTEs. Next, we’ll cover specific patterns for common problems: calculating ratios safely, replacing slow correlated subqueries, and knowing when a simple subquery is still the right choice.