SQL WITH (CTE): break a question into steps

WITH monthly AS (SELECT …) SELECT … FROM monthly works like creating a temporary table for one query.

Chain several CTEs, separated by commas, to stage the logic: first clean, then aggregate, then compare.

Worked example

What was paid revenue per month, and which months were above €3,000?

WITH monthly AS (
  SELECT strftime('%Y-%m', invoice_date) AS month, SUM(amount) AS revenue
  FROM invoices
  WHERE status = 'paid'
  GROUP BY month
)
SELECT month, revenue
FROM monthly
WHERE revenue > 3000
ORDER BY month
LIMIT 6;

Common mistakes

• Forgetting the comma between two CTEs.
• Ending the CTE without a final SELECT that uses it.