CTEs & query decomposition

A Common Table Expression (CTE) gives a name to an intermediate query result for the duration of one statement. CTEs are not automatically “faster”; their primary benefit is clearer structure, and optimizer behavior differs by engine/version.

Basic WITH query

WITH paid_orders AS (
  SELECT id, customer_id, total
  FROM orders
  WHERE status = 'paid'
)
SELECT customer_id,
       COUNT(*) AS paid_orders,
       SUM(total) AS paid_total
FROM paid_orders
GROUP BY customer_id
ORDER BY paid_total DESC;

Run the query to see the result.

Multiple CTEs as a pipeline

WITH customer_totals AS (
  SELECT customer_id, SUM(total) AS paid_total
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
),
ranked AS (
  SELECT customer_id,
         paid_total,
         ROW_NUMBER() OVER (ORDER BY paid_total DESC, customer_id) AS position
  FROM customer_totals
)
SELECT customer_id, paid_total, position
FROM ranked
ORDER BY position;

Run the query to see the result.

Each step should have a meaningful name and a clear grain.

CTE versus subquery

A CTE and an inline subquery can express the same relational logic. Prefer the form that makes the query easier to verify. For a multi-stage QA reconciliation, named stages such as normalized_expected, normalized_actual, and differences can dramatically improve reviewability.

Recursive CTE concept

Recursive CTEs repeatedly apply a query until no new rows are produced. They are used for trees, graphs, hierarchical paths, sequence generation, and dependency traversal. They need an anchor term, a recursive term, and a termination condition.

A recursive query is powerful enough to create runaway work if the termination rule is wrong, so test it on bounded data first.

CTE performance caution

Do not assume that extracting logic into a CTE improves execution speed. PostgreSQL can inline or materialize CTEs depending on the query and version; other engines have different rules. Read the execution plan when performance matters.

Practice: build a CTE containing only QA employees, then query it for the highest score and average salary.