Apple-style Company ChallengeMediumVerified answerSQLite live

Department Budget vs Salary Cost

For each department, how does the total salary cost compare to the budget, and what is the average salary?

  • Joins
  • Aggregation
  • Numeric functions
  • NULL handling
  • Sorting

Challenge brief

Understand the request

HR Finance is reviewing compensation spend against approved departmental budgets ahead of headcount planning.

Show department budget, actual salary cost, average salary, and remaining budget by joining employees to departments.

Return

  • department_name
  • budget
  • head_count
  • avg_salary (rounded 2)
  • total_salary_cost
  • remaining_budget (budget minus total salary)

Constraints

  • Include every department, including departments with no employees
  • Report zero salary cost and the full budget remaining for empty departments
  • Show the largest budgets first

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

departments

  • department_idINTEGER
  • department_nameVARCHAR(100)
  • budgetINTEGER
  • head_countINTEGER

employees

  • employee_idINTEGER
  • department_idINTEGER
  • salaryINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Budget is in departments. Salaries are in employees. INNER JOIN on department_id. GROUP BY department. AVG and SUM the salaries. Subtract SUM from budget for remaining.

Hint 2

INNER JOIN departments to employees on department_id. GROUP BY d.department_id (plus all non-aggregated columns). ROUND(AVG(e.salary), 2), SUM(e.salary), d.budget - SUM(e.salary) AS remaining_budget.

Hint 3

Build question 15 from its business grain: identify the driving rows, add only valid relationships, then apply the required filtering, aggregation, and deterministic ordering.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT d.department_name, d.budget, d.head_count, ROUND(AVG(e.salary), 2) AS avg_salary, COALESCE(SUM(e.salary), 0) AS total_salary_cost, d.budget - COALESCE(SUM(e.salary), 0) AS remaining_budget FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id, d.department_name, d.budget, d.head_count ORDER BY d.budget DESC, d.department_id;

Why this works

Drive from departments and retain missing employee matches. SUM is converted to zero for an empty department, while AVG remains null because no employee salary exists.

Success check

All 7 departments appear; Services has no employees, zero salary cost, null average salary, and its full $12 million budget remaining.

Expected result

Use this output to verify values, aliases, ordering, and row count.

department_namebudgethead_countavg_salarytotal_salary_costremaining_budget
Software Engineering500000003193333.3358000049420000
Hardware Engineering40000000223500047000039530000
Retail25000000215750031500024685000
Design15000000118000018000014820000
Services120000000NULL012000000
Executive1000000013000003000009700000
Environment800000012000002000007800000

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.