CTEs and subqueries

CTEs

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS mth, SUM(total) AS revenue
  FROM orders GROUP BY 1
),
with_growth AS (
  SELECT mth, revenue,
         revenue - LAG(revenue) OVER (ORDER BY mth) AS mom_change
  FROM monthly
)
SELECT * FROM with_growth WHERE mom_change < 0;

CTEs make a long query readable top-to-bottom instead of inside-out. They are the single biggest readability win available in SQL and are what interviewers hope to see on a multi-step problem.

One caveat: in some engines a CTE is an optimisation fence (older PostgreSQL always materialised them). Modern Postgres inlines unless you write MATERIALIZED.

Correlated subqueries

An uncorrelated subquery runs once. A correlated one references the outer query and conceptually runs per outer row:

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Correlated subqueries can be slow, and a join or window function is often faster. But EXISTS short-circuits on the first match, so for a pure existence check it is usually efficient — and unlike NOT IN, NOT EXISTS handles NULLs correctly.

IN vs EXISTS vs JOIN

IN reads well for a small literal list. EXISTS is the safe choice for a subquery, especially negated. A JOIN is right when you need columns from the other table — but remember it can multiply rows, which EXISTS never does.

That last point is the practical reason to reach for EXISTS: it answers "does a match exist" without touching your row count.

Recursive CTEs

WITH RECURSIVE chain AS (
  SELECT id, manager_id, name, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, c.depth + 1
  FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain ORDER BY depth;

An anchor member, UNION ALL, then a recursive member referencing the CTE. This walks org charts, category trees and date spines. Always ensure the recursion terminates — add a depth guard on data you do not fully trust, or a cycle will run until the engine stops it.