Which Team Is Best-Funded per Person?
Finance at Kestrel Labs is reviewing next year's allocations and the meeting keeps stalling on the same objection. Engineering has by far the largest budget, which sounds generous until somebody points out that Engineering is also one of the larger teams. Raw budget makes big teams look spoiled and small teams look starved, so Finance wants the figure that actually compares: how much budget each team has behind every person on it.
Neither table can produce that on its own. departments holds the money and nothing about people; employees holds the people and nothing about money at team level. The headcount that the calculation needs is not stored anywhere at all — it exists only once employee rows are gathered up by team.
departments — one row per team. budget is the annual figure in dollars.
| id | name | budget |
|---|---|---|
| 1 | Engineering | 900000 |
| 2 | Sales | 400000 |
| 3 | Marketing | 250000 |
| 4 | Data | 500000 |
employees — one row per person. department_id says which team they sit on.
| 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning two columns, name — the team's name, from departments — and budget_per_head, that team's budget spread evenly across the people on it, rounded to 2 decimal places. Only teams with at least one person appear; a team with nobody on it produces no row rather than a row with an empty or infinite figure. Rows come back best-funded first, ties settled on the team's name alphabetically.
Example output — shape only; the figures below are invented, not this data's answer.
| name | budget_per_head |
|---|---|
| Research | 410000 |
| Facilities | 92500.5 |
| Legal | 61250.75 |
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