CTE

-- Basic CTE
WITH active_users AS (
    SELECT id, name FROM users WHERE active = true
),
recent_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY user_id
)
SELECT au.name, COALESCE(ro.order_count, 0) AS orders
FROM active_users au
LEFT JOIN recent_orders ro ON au.id = ro.user_id
ORDER BY orders DESC;

-- Multiple CTE references
WITH base AS (
    SELECT dept, salary FROM employees
),
stats AS (
    SELECT dept, AVG(salary) AS avg_sal FROM base GROUP BY dept
)
SELECT * FROM stats WHERE avg_sal > 50000;