Everyone who reports to a manager, at any level

Hard

Question

The employees table stores each person with the id of their manager (manager_id, NULL for the top boss). The ids are not in any useful order.

Find everybody who reports to the manager named Sunita, directly or through other managers, at any depth.

Return name and level. A person who reports directly to Sunita has level 1, a person who reports to one of those has level 2, and so on. Sunita herself is not part of the result. The order of the rows does not matter.

recursive cte
hierarchy
tree
self reference

Read first: Lesson 46, SQL queries inside queries: subqueries, EXISTS, CTEs and recursion

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

employees3 columns · 14 rowsnot run yet

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

Example rows of the employees table
id	name	manager_id
14	Rajesh	NULL
3	Sunita	14
11	Vikram	14
8	Anjali	3
2	Karan	3
12	Leela	11
5	Manoj	8
9	Nisha	8
1	Omkar	2
13	Pooja	12
7	Qasim	5
4	Riya	5
10	Sanjay	1
6	Tara	7

About this SQL

SQLite, Postgres and MySQL 8 write WITH RECURSIVE (SQL Server and Oracle leave the word RECURSIVE out). A recursive CTE has a starting query, UNION or UNION ALL, and a query that joins the CTE to the table again. It stops when the second part returns no new rows. If the data has a loop (A manages B, B manages A), UNION ALL runs forever, so use UNION or a depth limit; the 5 second limit of this round will stop it.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready