CTEs — WITH Clause
Common Table Expressions for readable, reusable query logic
Skills taught: sql.cte, sql.with-clause, sql.recursive-cte
Basic CTEs
-- Simple CTE: name a subquery
WITH high_value_orders AS (
SELECT *
FROM orders
WHERE total > 1000
)
SELECT u.name, h.total, h.ordered_at
FROM high_value_orders h
JOIN users u ON h.user_id = u.id;
-- Multiple CTEs chained
WITH
order_stats AS (
SELECT
user_id,
COUNT(*) AS order_count,
SUM(total) AS total_spent,
ROUND(AVG(total), 2) AS avg_order
FROM orders
GROUP BY user_id
),
top_customers AS (
SELECT * FROM order_stats
WHERE total_spent > 1000
)
SELECT
u.name,
tc.order_count,
tc.total_spent,
tc.avg_order
FROM top_customers tc
JOIN users u ON tc.user_id = u.id
ORDER BY tc.total_spent DESC;
Recursive CTEs
-- Generate a number series 1 to 10
WITH RECURSIVE numbers AS (
SELECT 1 AS n -- base case
UNION ALL
SELECT n + 1 -- recursive case
FROM numbers
WHERE n < 10 -- stop condition!
)
SELECT n FROM numbers;
-- Generate dates for the next 7 days
WITH RECURSIVE dates AS (
SELECT DATE('2024-06-01') AS d
UNION ALL
SELECT DATE(d, '+1 day')
FROM dates
WHERE d < DATE('2024-06-07')
)
SELECT d FROM dates;
-- Hierarchy: employee → manager chain
-- (if we had an employees table with manager_id)
-- WITH RECURSIVE org AS (
-- SELECT id, name, manager_id, 1 AS depth
-- FROM employees WHERE manager_id IS NULL
-- UNION ALL
-- SELECT e.id, e.name, e.manager_id, org.depth + 1
-- FROM employees e JOIN org ON e.manager_id = org.id
-- )
-- SELECT * FROM org ORDER BY depth, name;
Frequently Asked Questions
Can a CTE reference another CTE defined above it?
Yes! CTEs are defined in order and each one can reference any CTE defined before it in the same WITH clause. This is how you chain steps: WITH a AS (...), b AS (... FROM a ...), c AS (... FROM b ...) SELECT ... FROM c.