CTEs and the WITH Clause

Learning Focus

Use this lesson to master CTEs — named subqueries that make complex SQL readable and reusable.

What is a CTE?

A Common Table Expression (CTE) is a named, temporary result set defined at the start of a query using the WITH keyword. It can be referenced like a table within the main query.

Benefits:

• Break complex queries into readable steps
• Reference the same subquery multiple times without repeating it
• Required for recursive queries

Basic Syntax

WITH cte_name AS (
    SELECT ... FROM ...
)
SELECT * FROM cte_name;

Examples

Example 1: Basic CTE

WITH high_earners AS (
    SELECT name, department, salary
    FROM employees
    WHERE salary > 80000
)
SELECT department, COUNT(*) AS count, AVG(salary) AS avg_salary
FROM high_earners
GROUP BY department;

Example 2: Multiple CTEs (Chained)

WITH
dept_stats AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
above_avg_depts AS (
    SELECT department
    FROM dept_stats
    WHERE avg_salary > 80000
)
SELECT e.name, e.salary, e.department
FROM employees e
INNER JOIN above_avg_depts d ON e.department = d.department
ORDER BY e.salary DESC;

Example 3: CTE for Deduplication

WITH ranked AS (
    SELECT *,
        row_number() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
)
SELECT * FROM ranked WHERE rn = 1;

Example 4: Recursive CTE (Hierarchy)

-- Employee → Manager hierarchy traversal
WITH RECURSIVE emp_hierarchy AS (
    -- Base case: top-level managers (no manager)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: employees reporting to someone in hierarchy
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    INNER JOIN emp_hierarchy h ON e.manager_id = h.id
)
SELECT name, level FROM emp_hierarchy ORDER BY level, name;

CTE vs Subquery

Aspect	CTE	Subquery
Readability	High — named and reusable	Low — nested and inlined
Reuse	Can reference multiple times	Must repeat
Recursive	Supported	Not supported
Performance	DuckDB materializes CTEs when referenced multiple times	Inline execution

Common Pitfalls

Pitfall	Consequence	Prevention
CTE column not found	Error — CTE isn't in scope	CTEs are only scoped to the query they're in
Duplicate CTE names	Error	Each CTE must have a unique name
Recursion without base case	Infinite loop	Always include a WHERE termination condition

Quick Reference

WITH cte AS (SELECT ...)
SELECT * FROM cte;

WITH a AS (...), b AS (SELECT * FROM a WHERE ...)
SELECT * FROM b;

WITH RECURSIVE r AS (SELECT ... UNION ALL SELECT ... FROM r WHERE ...)
SELECT * FROM r;

What's Next

• Next: Window Functions - Compute rolling totals, ranks, and moving averages.
• Module Overview - Return to this module index.