Common Table Expressions (WITH)

A CTE defines a named, temporary result set that you can reference in the main query — like a view that lives only for the duration of the query.

Syntax

WITH cte_name AS (
  SELECT ...
)
SELECT *
FROM cte_name
WHERE ...;

Example: top customers by spend

WITH customer_spend AS (
  SELECT
    o.customer_id,
    SUM(oi.quantity * oi.unit_price) AS total_spent
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.customer_id
)
SELECT
  c.first_name,
  c.last_name,
  cs.total_spent
FROM customer_spend cs
JOIN customers c ON c.id = cs.customer_id
ORDER BY cs.total_spent DESC
LIMIT 10;

Multiple CTEs

Separate them with a comma. Each CTE can reference the ones before it:

WITH
  dept_stats AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
  ),
  top_depts AS (
    SELECT * FROM dept_stats WHERE avg_sal > 9000
  )
SELECT d.name, td.avg_sal
FROM top_depts td
JOIN departments d ON d.id = td.department_id;

CTE vs subquery

Tips

• Name CTEs after what they represent (customer_spend, dept_stats) not what they do (step1, temp).
• In PostgreSQL, CTEs are not materialized by default (the optimizer may inline them). Use AS MATERIALIZED (...) to force it.
• Recursive CTEs (WITH RECURSIVE) can traverse tree structures like org charts.