sqladvanced15 minutes

Build a SQL Function to Generate a Recursive Hierarchical Employee Path

Create a SQL function that, given an employee ID, returns the full management chain from the employee up to the top-level manager as a single concatenated string.

Challenge prompt

You have an employee table structured as (employee_id, employee_name, manager_id), where manager_id references employee_id for that employee's manager. Write a SQL function build_employee_path(employee_id INT) that recursively constructs and returns a string concatenating the employee's name and all their managers' names in order from the employee up to the top-level manager, separated by ' > '. For example, if Alice reports to Bob who reports to Carol, calling build_employee_path for Alice returns 'Alice > Bob > Carol'.

Guidance

• Use a recursive common table expression (CTE) to walk up the management hierarchy from the given employee.
• Concatenate employee names at each recursion step to build the complete management chain path.
• Handle the base case where the employee has no manager (top-level manager).

Hints

• You can use a recursive CTE with UNION ALL to traverse parent managers.
• Consider string aggregation functions or recursive concatenation within the CTE to build the path.
• Be mindful of potential cycles in the hierarchy and guard against infinite recursion.

Starter code

CREATE OR REPLACE FUNCTION build_employee_path(emp_id INT) RETURNS TEXT AS $$
WITH RECURSIVE management_path AS (
    SELECT employee_id, employee_name, manager_id, employee_name::TEXT AS path
    FROM employees
    WHERE employee_id = emp_id
    UNION ALL
    SELECT e.employee_id, e.employee_name, e.manager_id, mp.path || ' > ' || e.employee_name
    FROM employees e
    JOIN management_path mp ON e.employee_id = mp.manager_id
)
SELECT path FROM management_path
WHERE manager_id IS NULL
LIMIT 1;
$$ LANGUAGE SQL;

Expected output

For employee_id = 1 corresponding to Alice, output could be: 'Alice > Bob > Carol'

Core concepts

recursive CTEstring concatenationhierarchical queriesSQL functions

Challenge a Friend

Send this duel to someone else and see if they can solve it.