Subqueries and CTEs

Sometimes a query needs the result of another query as input. SQL gives you two ways: subqueries (nested) and CTEs (named, with WITH). CTEs almost always read better.

Inline subqueries

SELECT *
FROM customers
WHERE id IN (
  SELECT customer_id FROM orders WHERE total_amount > 1000
);

Returns customers who have at least one order over $1,000. The subquery in the IN clause produces a list of customer_ids; the outer query filters to those.

Common Table Expressions (CTEs)

Same query, but with a WITH clause. Easier to read, easier to debug, and the CTE can be reused later in the same query.

WITH big_spenders AS (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE total_amount > 1000
)
SELECT c.*
FROM customers c
JOIN big_spenders b ON b.customer_id = c.id;

Multiple CTEs

WITH 
  monthly_revenue AS (
    SELECT date_trunc('month', created_at) AS month, SUM(total_amount) AS revenue
    FROM orders
    GROUP BY 1
  ),
  monthly_avg AS (
    SELECT AVG(revenue) AS avg_rev FROM monthly_revenue
  )
SELECT mr.month, mr.revenue, ma.avg_rev,
       mr.revenue - ma.avg_rev AS deviation_from_avg
FROM monthly_revenue mr, monthly_avg ma
ORDER BY mr.month;

Two CTEs chained together. First computes monthly revenue; second computes the all-time average; the final SELECT compares each month to the average. This kind of layered analysis is where CTEs really shine — every step is named and auditable.