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

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