Skip to content
RED SAGSkills
hard60 ptsRecursive CteStrings

Org Chart Reporting Chain

employees.manager_id points to the employee's direct manager. Top-level employees have a NULL manager_id. Some rows point to a manager who is not in the table; those employees and everyone under them are not part of any chart and must be left out. There are no cycles.

For every employee reachable from a top-level employee, report:

- depth: 1 for a top-level employee, 2 for their direct reports, and so on - chain: names from the top down to the employee, joined with ' > ' (for example Amal > Badr > Cora)

Return columns emp_id, name, depth, chain. Order by emp_id ascending.

Schema

CREATE TABLE employees (
  emp_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  manager_id INTEGER
);

Sample data

employees

emp_idnamemanager_id
1AmalNULL
2Badr1
3Cora2
4Dax1
5Eli3
6Fern4
Hint 1

Anchor the recursion on rows where manager_id IS NULL.

Hint 2

Each recursive step joins employees to the rows found so far, adds 1 to depth and extends the chain string.

Hint 3

Orphans never join to a tree row, so the recursion leaves them out by itself.

Good practice for: Backend Developer, Data Engineer, Database Administrator, Full Stack Developer

SQL (SQLite)

Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.