SQL CTEs: Write Readable Multi-Step Queries

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spend
  FROM orders
  GROUP BY customer_id
),
avg_spend AS (
  SELECT AVG(total_spend) AS avg_total
  FROM customer_totals
)
SELECT ct.customer_id, ct.total_spend, a.avg_total
FROM customer_totals ct
CROSS JOIN avg_spend a
WHERE ct.total_spend > a.avg_total
ORDER BY ct.total_spend DESC;

First CTE (customer_totals) calculates each customer's total spending. Second CTE (avg_spend) calculates the average. The final SELECT joins them and filters for above-average spenders. Each step is named and readable.

1. WITH opens the CTE block — you can chain multiple CTEs with commas
2. customer_totals groups orders by customer and sums amounts
3. avg_spend calculates the average of those totals
4. Final SELECT joins both CTEs and filters for above-average
5. ORDER BY sorts results by spending descending

• CTEs are not materialized in most databases — they're re-evaluated each time referenced
• For performance on repeated references, consider a temp table instead
• Recursive CTEs need a UNION ALL and a termination condition to avoid infinite loops
• CTE names can't conflict with table names in the same query