Recursive CTE Employee Hierarchy Query – Name Manager

Task: Write a complete, production‑ready SQL script that uses a recursive Common Table Expression (CTE) to retrieve an employee hierarchy from a relational database. The script must be ready to run on a standard ANSI‑SQL compliant system (e.g., PostgreSQL, SQL Server, MySQL 8.0+).

Requirements:

1. Source Table: Assume a single table named employees with the following columns:

   • employee_id (PK, integer)
   • manager_id (FK to employees.employee_id, nullable) – NULL indicates a top‑level executive.
   • first_name (varchar)
   • last_name (varchar)
   • title (varchar)
   • department_id (integer)
   • salary (numeric)

2. Recursive CTE Output Columns:

   • employee_id
   • full_name – concatenation of first_name and last_name.
   • title
   • department_id
   • salary
   • manager_id
   • hierarchy_level – integer starting at 1 for the root node(s).
   • path – a slash‑separated string of employee IDs from the root to the current row (e.g., 1/4/7).
   • total_subtree_salary – the sum of salaries for the employee and all of their direct and indirect reports.

3. Parameters:

   • @start_manager_id (or a placeholder like :start_manager_id) – the employee ID from which to begin the hierarchy. If NULL, the CTE should start from all top‑level executives (manager_id IS NULL).

4. Cycle Detection: The query must safely detect and break potential reporting loops (e.g., an employee mistakenly set as their own manager) by limiting recursion depth to a reasonable maximum (e.g., 100) and excluding rows that would cause a cycle.

5. Ordering: Final result set should be ordered first by hierarchy_level ascending, then by path ascending.

6. Performance Considerations: Include appropriate indexes (as comments) that would support the recursive query efficiently.

7. Output Format: Provide the following sections, each clearly labeled:

   • SQL Script: The full query wrapped in a markdown code block (sql).
   • Explanation: A concise, bullet‑point description of how the CTE works, how the parameters are used, and how cycle detection is implemented.
   • Index Recommendations: SQL CREATE INDEX statements (as comments) that would improve performance for large employee tables.

Example Output:

### SQL Script
```sql
-- Replace :start_manager_id with the desired root employee ID or leave NULL for all CEOs
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member: start nodes
    SELECT
        e.employee_id,
        CONCAT(e.first_name, ' ', e.last_name) AS full_name,
        e.title,
        e.department_id,
        e.salary,
        e.manager_id,
        1 AS hierarchy_level,
        CAST(e.employee_id AS VARCHAR(1000)) AS path,
        e.salary AS total_subtree_salary,
        ARRAY[e.employee_id] AS visited_ids          -- for cycle detection
    FROM employees e
    WHERE ( :start_manager_id IS NULL AND e.manager_id IS NULL )
       OR e.employee_id = :start_manager_id

    UNION ALL

    -- Recursive member: walk down the hierarchy
    SELECT
        e.employee_id,
        CONCAT(e.first_name, ' ', e.last_name) AS full_name,
        e.title,
        e.department_id,
        e.salary,
        e.manager_id,
        eh.hierarchy_level + 1,
        CONCAT(eh.path, '/', e.employee_id),
        eh.total_subtree_salary + e.salary,
        visited_ids || e.employee_id
    FROM employees e
    JOIN employee_hierarchy eh
      ON e.manager_id = eh.employee_id
    WHERE NOT e.employee_id = ANY(eh.visited_ids)      -- prevent cycles
      AND eh.hierarchy_level < 100                     -- safety cap
)
SELECT
    employee_id,
    full_name,
    title,
    department_id,
    salary,
    manager_id,
    hierarchy_level,
    path,
    total_subtree_salary
FROM employee_hierarchy
ORDER BY hierarchy_level, path;

Explanation

• Anchor member selects the root(s) based on :start_manager_id.
• Recursive member joins each manager to their direct reports, incrementing hierarchy_level and extending the path.
• visited_ids (an array) tracks IDs already seen in the current branch; the WHERE NOT … = ANY(visited_ids) clause prevents infinite loops caused by circular references.
• The recursion depth is limited to 100 to guard against pathological data.
• total_subtree_salary aggregates salaries on‑the‑fly, giving the cumulative cost for each manager’s entire reporting tree.

Index Recommendations

-- Index to quickly locate an employee’s direct reports
CREATE INDEX idx_employees_manager_id ON employees(manager_id);

-- Index to support look‑ups by employee_id (usually already PK)
CREATE INDEX idx_employees_employee_id ON employees(employee_id);