PostgreSQL — CTE

Basic CTE

WITH active_users AS (
    SELECT * FROM users WHERE active = true
)
SELECT * FROM active_users;

Multiple CTEs

WITH 
active_users AS (
    SELECT * FROM users WHERE active = true
),
recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT u.name, o.total
FROM active_users u
JOIN recent_orders o ON u.id = o.user_id;

Recursive CTE

WITH RECURSIVE category_tree AS (
    -- Base case
    SELECT id, name, parent_id, 0 as depth
    FROM categories WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Recursive case
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

CTE Benefits

Benefit	Description
Readability	Named subqueries
Reusability	Use multiple times
Recursion	Hierarchical data
Performance	Optimized execution

Mini Practice

1. Create a basic CTE
2. Use multiple CTEs
3. Create recursive CTE
4. Query CTE results

Up Next

Continue with Views — creating views.