How Big Is Each Team?
You have just joined Kestrel Labs as its first People Analytics hire. The chief of staff is drawing up the seating plan for next month's all-hands and needs to know how many chairs each team will fill.
The employee export will not simply tell you. It has one row per person and no summary line anywhere, so the ten rows have to become one number per team. Each row carries a plain team number in department_id, and a number is all the seating plan needs — the team names get pencilled in by hand afterwards.
employees — one row per employee. department_id is the team the person sits on. manager_id points at another employee's id, or is NULL for someone 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 two columns, department_id and num_employees, with one row for every team that has at least one person on it. A team with nobody on it produces no row at all rather than a row reading zero. Rows come back sorted by department_id, smallest first.
Example output — shape only, on an invented company; both the ids and the counts are made up.
| department_id | num_employees |
|---|---|
| 5 | 9 |
| 6 | 12 |
| 8 | 7 |
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