Common Table Expressions (WITH)

Name a subquery to make queries readable.

Name your steps

A CTE (WITH) names a query so you can build on it — like a temporary, query-scoped view. It turns a nested mess into readable steps. "The revenue of each order, then only the big ones":

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS revenue
  FROM order_items
  GROUP BY order_id
)
SELECT order_id, revenue
FROM order_totals
WHERE revenue > 30
ORDER BY revenue DESC;

The CTE computes once; the main query reads from it by name. You can chain several CTEs, comma-separated.