Welcome to Day 8! ✨
Today we’ll learn how to write cleaner, modular SQL using Common Table Expressions (CTEs) with the WITH clause. CTEs help you break complex queries into readable steps and can be reused within a single query.
What You’ll Learn Today
- CTE basics and syntax
- Naming and referencing CTEs
- Multiple CTEs and chaining
- Recursive CTEs
- Using CTEs for readability and performance
- Comparing CTEs vs subqueries
1. CTE Basics and Syntax
A CTE defines a temporary result set available only for the next statement.
WITH cte_name AS (
SELECT columns
FROM table
WHERE conditions
)
SELECT *
FROM cte_name;
Example
WITH high_earners AS (
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 80000
)
SELECT *
FROM high_earners
ORDER BY salary DESC;
2. Naming and Referencing CTEs
You can select specific columns or use aliases.
WITH department_stats(dept, employee_count, avg_salary) AS (
SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department
)
SELECT dept, employee_count, ROUND(avg_salary, 2)
FROM department_stats
ORDER BY employee_count DESC;
3. Multiple CTEs and Chaining
You can define multiple CTEs and reference earlier ones.
WITH
orders_2024 AS (
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024
),
totals AS (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders_2024
GROUP BY customer_id
),
top_customers AS (
SELECT customer_id, total_spent
FROM totals
WHERE total_spent > 1000
)
SELECT c.customer_id, c.customer_name, t.total_spent
FROM customers c
JOIN top_customers t USING (customer_id)
ORDER BY t.total_spent DESC;
4. Recursive CTEs
Recursive CTEs reference themselves to traverse hierarchies or sequences.
Example: Organization Hierarchy
WITH RECURSIVE org AS (
-- Anchor member
SELECT employee_id, manager_id, first_name, 0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive member
SELECT e.employee_id, e.manager_id, e.first_name, org.level + 1
FROM employees e
JOIN org ON e.manager_id = org.employee_id
)
SELECT *
FROM org
ORDER BY level, employee_id;
Example: Generate a Sequence
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 12
)
SELECT * FROM seq;
5. Readability and Performance
- CTEs improve readability by splitting logic into steps
- They can be reused within a query, reducing repetition
- In PostgreSQL, CTEs used to act as optimization fences (pre-12). From PG12+, they may be inlined by the optimizer
- Use CTEs when they clarify intent; test performance with
EXPLAIN ANALYZE
EXPLAIN ANALYZE
WITH filtered AS (
SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),
agg AS (
SELECT customer_id, SUM(total_amount) AS total_30d
FROM filtered
GROUP BY customer_id
)
SELECT * FROM agg ORDER BY total_30d DESC;
6. CTEs vs Subqueries
- Use CTEs for readability, reuse, and recursion
- Use subqueries for simple one-off filters or comparisons
- Both can often achieve the same results; choose the clearer option
Real-World Example: Monthly Sales Pipeline
WITH
monthly_orders AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
customer_id,
SUM(total_amount) AS monthly_spent
FROM orders
GROUP BY DATE_TRUNC('month', order_date), customer_id
),
cohort AS (
SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS first_month
FROM orders
GROUP BY customer_id
),
retention AS (
SELECT
m.month,
COUNT(DISTINCT m.customer_id) AS active_customers
FROM monthly_orders m
GROUP BY m.month
)
SELECT month, active_customers
FROM retention
ORDER BY month;
Common Mistakes to Avoid
-- ❌ WRONG: Missing comma between CTEs
WITH a AS (...)
b AS (...)
SELECT ...;
-- ✅ CORRECT: Add comma between CTE blocks
WITH a AS (...),
b AS (...)
SELECT ...;
-- ❌ WRONG: Forgetting RECURSIVE for self-referencing CTE
WITH chain AS (
SELECT 1 AS n UNION ALL SELECT n+1 FROM chain
)
-- ✅ CORRECT
WITH RECURSIVE chain AS (
SELECT 1 AS n UNION ALL SELECT n+1 FROM chain
)
SELECT * FROM chain WHERE n <= 10;
Today’s Challenge! 🎯
Use CTEs to answer these questions:
- For each department, compute employee count and average salary using a CTE, then select only departments with avg salary > $60,000
- Build a recursive CTE to list all subordinates under a given manager (by manager_id), with levels
- Create a CTE pipeline to compute each customer’s total spend in the last 90 days and list the top 10
-- Your solutions here
Summary
Today you learned:
- ✅ How to write and chain CTEs
- ✅ Recursive CTEs for hierarchies and sequences
- ✅ When to use CTEs vs subqueries
- ✅ Real-world analysis patterns using CTEs
Coming Up Next!
Day 9: Window Functions — Analyze rows relative to other rows without collapsing results.
