Question

You have an Employees table with columns for employee names and their respective managers. How will you find the longest chain of reporting for each employee?

Answer 1

WITH RECURSIVE ReportingChain AS (

    SELECT employee_id, manager_id, employee_name, 1 AS chain_length

    FROM Employees

    WHERE manager_id IS NOT NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.employee_name, rc.chain_length + 1

    FROM Employees e

    INNER JOIN ReportingChain rc ON e.manager_id = rc.employee_id

)

SELECT employee_id, employee_name, MAX(chain_length) AS longest_chain

FROM ReportingChain

GROUP BY employee_id, employee_name;