Can you write this from memory?
Create a CTE named 'active_users' that selects all users where status = 'active', then select all columns from it.
A 50-line SQL query with nested subqueries is a nightmare to debug. The same logic as a chain of CTEs is readable. Each WITH block is a named step you can test independently.
These exercises cover WITH syntax, chaining multiple CTEs, and recursive queries for hierarchical data. Once CTEs click, you won't go back to deeply nested subqueries.
CTEs shine when a query has multiple transformation steps. Instead of nesting subqueries three levels deep, name each step:
WITH monthly_revenue AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY 1
),
growth AS (
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly_revenue
)
SELECT month, revenue,
ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 1) AS growth_pct
FROM growth;
This example combines CTEs with window functions — a common pattern for analytics queries. CTEs also simplify complex JOIN chains by pre-aggregating data to prevent row explosion.
PostgreSQL 12+ inlines non-recursive CTEs by default (treating them like subqueries for optimization). Add MATERIALIZED to force evaluation:
WITH MATERIALIZED expensive AS (
SELECT * FROM large_table WHERE complex_condition
)
SELECT * FROM expensive e1 JOIN expensive e2 ON e1.id = e2.parent_id;
MySQL always materializes CTEs. SQLite inlines when possible.
When to Use SQL CTEs
- Break complex queries into readable steps.
- Reuse a derived dataset multiple times in a query.
- Model hierarchies with recursive CTEs.
Check Your Understanding: SQL CTEs
Build a recursive query to fetch category hierarchies.
Use WITH RECURSIVE with a base case and a UNION ALL that joins to children.
What You'll Practice: SQL CTEs
Common SQL CTEs Pitfalls
- CTE scope is limited to the following statement
- Recursive CTEs need a base case
- Some databases materialize CTEs (performance impact)
SQL CTEs FAQ
How long does a CTE live?
Only for the single statement that follows it; it is not persisted.
Are CTEs always optimized?
Not always. Some databases materialize CTEs; check your engine and profile performance.
Can you use multiple CTEs in one query?
Yes. Use a single WITH keyword followed by comma-separated CTE definitions: `WITH cte1 AS (...), cte2 AS (...) SELECT ...`. Later CTEs can reference earlier ones. This is a clean alternative to deeply nested subqueries.
SQL CTEs Syntax Quick Reference
WITH active_users AS (
SELECT * FROM users
WHERE status = 'active'
)
SELECT * FROM active_users;WITH orders_2024 AS (
SELECT * FROM orders WHERE year = 2024
),
high_value AS (
SELECT * FROM orders_2024 WHERE amount > 1000
)
SELECT * FROM high_value;WITH RECURSIVE hierarchy AS (
SELECT id, name, 1 as level FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, h.level + 1
FROM categories c JOIN hierarchy h ON c.parent_id = h.id
)
SELECT * FROM hierarchy;SQL CTEs Sample Exercises
Fill in the keyword to define a Common Table Expression
WITHCan a CTE defined in one query be used in a completely separate query?
noFill in the separator between multiple CTE definitions
,+ 8 more exercises