CTE

Common Table Expressions (`WITH`) improve readability and enable recursion. In PostgreSQL 12+, non-recursive CTEs are often inlined; older versions materialized them by default, which could help or hurt plans.

					-- Readable staging
WITH recent AS (
  SELECT * FROM orders
  WHERE created_at >= now() - interval '30 days'
),
totals AS (
  SELECT customer_id, sum(amount) AS total
  FROM recent
  GROUP BY customer_id
)
SELECT c.name, t.total
FROM totals t
JOIN customers c ON c.id = t.customer_id
ORDER BY t.total DESC
LIMIT 20;

-- Recursive hierarchy
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;
				

Always check `EXPLAIN`: a CTE that filters early may inline well; one that scans a huge table may still be expensive when materialized.

On interviews: contrast readability CTEs with subqueries, explain recursive CTE use cases (org charts, graphs), and note that "CTEs are always slow" is outdated advice for modern PostgreSQL.

Common pitfalls: assuming materialization without checking the plan; recursive CTEs without cycle guards; duplicating heavy scans across multiple CTE branches.

The trade-off is clarity versus planner control — CTEs organize complex SQL but still need plan verification under real data volumes.

Checklist:

• Use CTEs to name intermediate steps.
• Guard recursive queries against cycles and runaway depth.
• Compare EXPLAIN with and without CTE rewrite.
• Prefer one well-filtered base CTE over repeated subqueries.