CTEs (WITH Clause)

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

-- Calculate user stats, then filter
WITH user_stats AS (
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.name,
    s.order_count,
    s.total_spent
FROM users u
JOIN user_stats s ON u.id = s.user_id
WHERE s.total_spent > 1000;

WITH 
monthly_revenue AS (
    SELECT 
        strftime('%Y-%m', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    GROUP BY strftime('%Y-%m', created_at)
),
avg_revenue AS (
    SELECT AVG(revenue) AS avg_monthly
    FROM monthly_revenue
)
SELECT 
    m.month,
    m.revenue,
    a.avg_monthly,
    CASE 
        WHEN m.revenue > a.avg_monthly THEN 'Above Average'
        ELSE 'Below Average'
    END AS performance
FROM monthly_revenue m
CROSS JOIN avg_revenue a
ORDER BY m.month;

-- Generate a sequence of numbers
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;

-- Employee hierarchy
WITH RECURSIVE org_chart AS (
    -- Base: top-level employees (no manager)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: employees with managers
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;