Using Common Table Expressions (CTEs)

Subqueries are powerful, but they can become hard to read when you nest them deeply. Common Table Expressions (CTEs) solve that problem by letting you name an intermediate result and reference it later in the same statement. In the marketplace schema, CTEs make it easy to compose multi-step analysis - such as finding high-value customers, summarizing category activity, or generating date ranges for reporting.

Learning Goals

Define single and multiple CTEs with the marketplace tables
Reuse intermediate results to simplify complex joins and aggregates
Build a recursive CTE to generate derived data
Decide when a CTE is clearer than an inline subquery

CTE Basics

A CTE lives only for the duration of the query. It is defined with WITH and then referenced like a virtual table.

Basic CTE structure
WITH category_summary AS (
  SELECT category_id,
         COUNT(*) AS total_products,
         AVG(price) AS avg_price
  FROM product
  GROUP BY category_id
)
SELECT cat.category_name,
       cs.total_products,
       cs.avg_price
FROM category_summary AS cs
JOIN category AS cat ON cat.category_id = cs.category_id
ORDER BY cs.total_products DESC;

Here the CTE computes product statistics per category. The outer query simply joins to category for friendly names.

Multiple CTEs

You can chain multiple CTEs by separating them with commas. This is great when each step builds on the previous one.

Multiple CTEs for customer spend
WITH
march_orders AS (
  SELECT order_id,
         customer_id
  FROM orders
  WHERE order_date LIKE '2025-03%'
),
customer_spend AS (
  SELECT mo.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total_spend
  FROM march_orders AS mo
  JOIN order_item AS oi ON oi.order_id = mo.order_id
  GROUP BY mo.customer_id
)
SELECT CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       cs.total_spend
FROM customer_spend AS cs
JOIN customer AS c ON c.customer_id = cs.customer_id
WHERE cs.total_spend >= 60
ORDER BY cs.total_spend DESC;

The first CTE filters orders to a month; the second calculates customer spend; the final query lists customers with meaningful totals.

Recursive CTEs

Recursive CTEs reference themselves to generate rows repeatedly until a stopping condition is reached. Our schema is not hierarchical, but we can still use recursion to generate a list of upcoming reporting months.

Generate the next six months (MySQL)
WITH RECURSIVE month_plan AS (
  SELECT DATE('2025-03-01') AS month_start, 1 AS step
  UNION ALL
  SELECT DATE_ADD(month_start, INTERVAL 1 MONTH), step + 1
  FROM month_plan
  WHERE step < 6
)
SELECT DATE_FORMAT(month_start, '%Y-%m') AS report_month
FROM month_plan;

This produces a sequence of month labels for forecasting or scheduled reports.

CTEs vs. Subqueries

Many subqueries can be rewritten as CTEs. Choose the style that communicates intent best.

High-demand categories
-- As a correlated subquery
SELECT cat.category_name
FROM category AS cat
WHERE (
  SELECT COUNT(*)
  FROM order_item AS oi
  JOIN product AS p ON p.product_id = oi.product_id
  JOIN orders AS o ON o.order_id = oi.order_id
  WHERE p.category_id = cat.category_id
    AND o.order_date LIKE '2025-03%'
) >= 3;

-- As a CTE
WITH march_counts AS (
  SELECT p.category_id,
         COUNT(*) AS item_count
  FROM order_item AS oi
  JOIN product AS p ON p.product_id = oi.product_id
  JOIN orders AS o ON o.order_id = oi.order_id
  WHERE o.order_date LIKE '2025-03%'
  GROUP BY p.category_id
)
SELECT cat.category_name
FROM march_counts AS mc
JOIN category AS cat ON cat.category_id = mc.category_id
WHERE mc.item_count >= 3;

Both versions answer the same question, but the CTE separates the counting logic from the final filter.

Common Pitfalls

Missing RECURSIVE: Required when the CTE references itself.
Infinite recursion: Always include a condition (like step < 6) to stop the recursive branch.
Overuse: For simple lookups a plain query may be clearer; reserve CTEs for multi-step logic.
Scope assumptions: CTEs exist only for the statement in which they are defined - they do not persist between commands.
Performance surprises: Some databases materialize CTE results; others inline them. Test with your engine when performance matters.

Summary

CTEs let you name intermediate results and keep complex queries readable.
Multiple CTEs act like a recipe: each step feeds the next.
Recursive CTEs can generate synthetic rows even when your base tables are flat.
They are interchangeable with subqueries in many situations - choose whichever maximizes clarity.

Quiz

Show quiz

What keyword introduces a CTE?
A) WITH
B) CTE
C) DEFINE
D) TEMP
In the multi-CTE example, which step calculates total spend per customer?
A) march_orders
B) customer_spend
C) The final SELECT
D) A correlated subquery
Which clause prevents the recursive month generator from running forever?
A) UNION ALL
B) CASE
C) WHERE step < 6
D) JOIN
True or False: A CTE defined in one statement is available to reuse in the next statement you run.
A) True
B) False

Answers

CTE Basics​

Multiple CTEs​

Recursive CTEs​

CTEs vs. Subqueries​

Common Pitfalls​

Summary​

Quiz​