Budget Utilization Report
The annual report for Kestrel Labs carries a utilisation table: for each team, how much of the money it was given is already committed to salaries. Anything above forty percent gets a paragraph of explanation, because whatever is left over is all the team has for tooling, travel and everything else it does.
The two numbers involved sit on opposite sides of the database. What a team was given is recorded once against the team; what it pays out is recorded a person at a time. Getting them into the same fraction is the exercise.
departments — one row per team. budget is that team's annual budget in dollars.
| id | name | budget |
|---|---|---|
| 1 | Engineering | 900000 |
| 2 | Sales | 400000 |
| 3 | Marketing | 250000 |
| 4 | Data | 500000 |
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 |
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) and salary_pct_of_budget (the total salaries of that team as a percentage of that team's budget, rounded to one decimal place), one row per team, highest figure first. Give a percentage out of 100, not a fraction of 1. A team with no employees would not appear at all, since the figure is built from employee rows.
Example output — shape only, on invented teams. The figures are percentages out of 100: a team on 91.4 has committed nearly all its budget to pay:
| name | salary_pct_of_budget |
|---|---|
| Logistics | 91.4 |
| Legal | 58.3 |
| Support | 26.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