Part 83: Chaining Multiple CTEs in a Single Query

You can chain multiple CTEs sequentially, where subsequent CTEs reference previous ones:

1. Multi-Step Data Pipeline

WITH 
  MonthlyRevenue AS (
    SELECT DATE_TRUNC('month', order_date) AS order_month, SUM(total_amount) AS revenue
    FROM orders
    GROUP BY 1
  ),
  GrowthCalculation AS (
    SELECT 
      order_month,
      revenue,
      LAG(revenue) OVER (ORDER BY order_month) AS previous_month_revenue
    FROM MonthlyRevenue
  )
SELECT 
  order_month,
  revenue,
  previous_month_revenue,
  ROUND(((revenue - previous_month_revenue) / previous_month_revenue) * 100, 2) AS growth_percentage
FROM GrowthCalculation;

Key Takeaways

• Chain CTEs separated by commas under a single WITH keyword.

In Part 84, we explore Modifying CTEs: Data-Modifying Statements with RETURNING!