Advanced SQL

Common Table Expressions

A common table expression gives a statement-local name to a query result, making a multi-stage relational transformation readable without implying that every engine stores the intermediate rows.

A nonrecursive CTE is a named table expression scoped to one statement.
CTEs clarify data flow by naming semantic stages.
A CTE does not create an ordering guarantee.
Materialization is an optimizer and dialect question.
Multiple references preserve query semantics, not a single-evaluation promise.
CTEs compose but do not repair wrong cardinality.

Build customer value in explicit stages

Filter, aggregate, then classify

WITH paid_lines AS (
  SELECT o.customer_id, o.order_id,
         i.quantity * i.unit_price AS line_total
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  WHERE o.status = 'PAID'
), customer_totals AS (
  SELECT customer_id, COUNT(DISTINCT order_id) AS order_count,
         SUM(line_total) AS revenue
  FROM paid_lines
  GROUP BY customer_id
)
SELECT customer_id, order_count, revenue,
       CASE WHEN revenue >= 1000 THEN 'high-value' ELSE 'standard' END AS segment
FROM customer_totals
ORDER BY revenue DESC, customer_id;

Stage contracts
Stage	Grain	Invariant
`paid_lines`	One row per order item	Only paid orders; line total is derived once
`customer_totals`	One row per customer	Revenue is the sum of paid lines
Final result	One row per customer	Segment derives from the completed total

For a customer with paid line totals 40, 60, and 900, the first stage emits three rows, the second emits one row with revenue 1000, and the final stage labels it high-value. This progression makes both multiplicity and business rules inspectable.