Common Table Expressions

A Common Table Expression (CTE) is a named temporary result set within a single statement.

CTEs improve readability by decomposing queries into logical steps.

Core Concepts

flowchart TD
  A[WITH cte AS (...)] --> B[CTE produces intermediate rows]
  B --> C[Main query references cte]
  C --> D[Final result]

CTE benefit	Why it helps
Named steps	Easier to read and review
Reuse in one query	Avoid repeated subquery text
Better structure	Clear pipeline of transformations

Single CTE

WITH category_totals AS (
  SELECT category, SUM(amount) AS total_amount
  FROM payments_agg
  GROUP BY category
)
SELECT category, total_amount
FROM category_totals
ORDER BY total_amount DESC;

category	total_amount
tools	300.0
cloud	130.0

Multiple CTEs

WITH category_totals AS (
  SELECT category, SUM(amount) AS total_amount
  FROM payments_agg
  GROUP BY category
),
ranked AS (
  SELECT category, total_amount
  FROM category_totals
  WHERE total_amount >= 150
)
SELECT *
FROM ranked
ORDER BY category;

category	total_amount
tools	300.0

Code Examples

-- CTE replacing nested subquery style.
WITH avg_price AS (
  SELECT AVG(price) AS value
  FROM products_sub
)
SELECT p.product_id, p.name, p.price
FROM products_sub AS p, avg_price AS a
WHERE p.price > a.value
ORDER BY p.price DESC;

product_id	name	price
3	Pro Keyboard	150.0
2	Keyboard	100.0

-- CTE with aggregation and post-filter.
WITH city_counts AS (
  SELECT city, COUNT(*) AS cnt
  FROM contacts_count
  GROUP BY city
)
SELECT city, cnt
FROM city_counts
WHERE cnt >= 2
ORDER BY city;

city	cnt
Berlin	2
Tokyo	2

SQLite-Specific Nuances

SQLite Nuance

SQLite supports non-recursive and recursive CTEs.

Start with non-recursive CTEs for readability before introducing recursion.

Common Pitfalls / Best Practices

Pitfall

Using too many one-off CTE layers for simple queries, making SQL longer without clarity gains.

Best Practice

Use CTEs when they improve naming and readability of meaningful intermediate steps.

Quick Challenge

Write a CTE that computes average amount from payments_agg, then selects payments above that average.

View Solution

WITH avg_amount AS (
  SELECT AVG(amount) AS value
  FROM payments_agg
)
SELECT payment_id, category, amount
FROM payments_agg, avg_amount
WHERE amount > avg_amount.value
ORDER BY amount DESC;

payment_id	category	amount
1	tools	120.0
5	tools	100.0