Span of Control
Leadership at Kestrel Labs has asked for an audit of the management structure, and the first line of it is a sanity check rather than a finding: of everyone on the books, how many actually have someone recorded above them?
The number matters because the reporting field is allowed to be empty, and an empty field means two very different things in practice — either the person genuinely reports to nobody, or somebody forgot to fill it in. The audit starts by measuring how big that pool is.
employees — one row per person. department_id is the team they sit on, given as the matching id in departments; salary is annual pay in dollars; hire_date is the day they started, written year-month-day; manager_id is the id of the person they report to, and is NULL for anyone who reports to nobody.
| 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 one row with a single column, with_manager, holding how many employees have a manager recorded — that is, rows whose manager_id points at another employee's id rather than being NULL.
Example output — the shape, on an invented figure. A company in which twenty-nine records name a manager would come back as:
| with_manager |
|---|
| 29 |
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