Chained CTEs — multiple WITH clauses

You can define several CTEs in one WITH block, separated by commas. Each CTE can reference the ones defined before it.

Syntax

WITH
  cte_one AS (
    SELECT ...
  ),
  cte_two AS (
    SELECT ... FROM cte_one WHERE ...
  )
SELECT *
FROM cte_two;

Why chain CTEs?

• Readability: split a complex query into clearly named steps.
• De-duplication: define expensive sub-results once and reference them multiple times.
• Debugging: temporarily SELECT * FROM cte_name to inspect intermediate results.

Example: top departments by average salary

WITH
  dept_stats AS (
    SELECT
      department_id,
      AVG(salary)  AS avg_salary,
      COUNT(*)     AS headcount
    FROM employees
    GROUP BY department_id
  ),
  ranked AS (
    SELECT *,
      RANK() OVER (ORDER BY avg_salary DESC) AS rnk
    FROM dept_stats
  )
SELECT d.name, r.avg_salary, r.headcount
FROM ranked r
JOIN departments d ON d.id = r.department_id
WHERE r.rnk <= 3
ORDER BY r.rnk;

Referencing a CTE multiple times

WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue
ORDER BY month;

Tips

• Name CTEs after what they represent (dept_stats, top_products) not after steps (step1, temp).
• In PostgreSQL, CTEs may be inlined (optimized away) unless you write AS MATERIALIZED (...).
• There is no practical limit on the number of CTEs in a single query.