Recursive CTE — Org Hierarchy Traversal

The manager_id self-reference in hr_employees forms a tree. A recursive CTE walks this tree top-down to compute reporting depth, headcount rollups, and the full chain of command for any employee.

-- Full org tree: every employee with depth and reporting chain
WITH RECURSIVE org_tree AS (
    -- Anchor: top-level employees (no manager)
    SELECT
        id,
        emp_id,
        name,
        manager_id,
        dept_id,
        job_level_id,
        0                                    AS depth,
        ARRAY[id]                            AS path,
        name                                 AS reporting_chain
    FROM hr_employees
    WHERE manager_id IS NULL AND is_active

    UNION ALL

    -- Recursive: employees reporting to someone in the tree
    SELECT
        e.id,
        e.emp_id,
        e.name,
        e.manager_id,
        e.dept_id,
        e.job_level_id,
        ot.depth + 1,
        ot.path || e.id,
        ot.reporting_chain || ' → ' || e.name
    FROM hr_employees e
    JOIN org_tree ot ON ot.id = e.manager_id
    WHERE e.is_active
)
SELECT
    REPEAT('  ', depth) || name              AS org_indent,
    emp_id,
    depth,
    ARRAY_LENGTH(path, 1)                    AS levels_deep,
    reporting_chain
FROM org_tree
ORDER BY path
LIMIT 30;