What is a Common Table Expression (CTE)?

-- Standard CTE
WITH HighEarners AS (
    SELECT name, department, salary
    FROM employees
    WHERE salary > 80000
)
SELECT department, COUNT(*) AS count
FROM HighEarners
GROUP BY department;

-- Recursive CTE for Hierarchical Org Chart
WITH RECURSIVE OrgChart AS (
    -- Anchor member
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    -- Recursive member
    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN OrgChart o ON e.manager_id = o.id
)
SELECT * FROM OrgChart;