Managers and Their Direct Reports
Kestrel Labs is generating the reporting lines for its org chart. Each line pairs a manager's name with the name of one person who reports to them, so a manager with three reports takes up three lines.
The awkward part is that both names come out of the same table. manager_id on an employee row holds the id of another employee row, so a manager and a report are the same kind of record, stored the same way, in the same place. The query has to treat one table as if it were two and keep straight which copy is playing which role.
employees — one row per employee. manager_id holds the id of that person's manager, or NULL if none is recorded.
| id | name | department_id | salary | hire_date | manager_id |
|---|---|---|---|---|---|
| 1 | Ava Chen | 1 | 145000 | 2021-03-14 | NULL |
| 2 | Ben Ortiz | 1 | 118000 | 2022-06-01 | 1 |
| 3 | Cara Novak | 1 | 121000 | 2023-01-10 | 1 |
| 4 | Deshawn Lee | 2 | 95000 | 2020-09-23 | NULL |
| 5 | Elin Kask | 2 | 88000 | 2022-11-05 | 4 |
| 6 | Farid Amiri | 3 | 76000 | 2023-04-18 | NULL |
| 7 | Grace Kim | 4 | 132000 | 2021-07-30 | NULL |
| 8 | Hugo Silva | 4 | 110000 | 2023-02-14 | 7 |
| 9 | Ines Duarte | 4 | 104000 | 2023-08-01 | 7 |
| 10 | Jonas Weber | 2 | 91000 | 2021-12-19 | 4 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning two columns, manager_name and report_name, with one row per manager-and-report pair. Rows come back in alphabetical order of manager_name, and within one manager, of report_name.
Example output — shape only, on an invented company. A manager of two people takes two rows, one per report.
| manager_name | report_name |
|---|---|
| Anton Reis | Lena Fournier |
| Anton Reis | Mira Kovac |
| Rosa Kwan | Yusuf Bello |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in