CTEs & Window Functions SQL practice problemHardVerified answer

Cumulative % of Department Salary Budget

Within each department, order employees by salary DESC and compute the cumulative salary and cumulative percentage of the department's total salary expense (rounded to 2). This shows how few top earners make up most of the cost. Return department_name, first_name, last_name, salary, cumulative_salary, cumulative_pct — ordered by department_name, salary DESC, employee_id.

  • Window functions
  • Joins
  • Aggregation
  • Sorting

Interview brief

Understand the request

Within each department, order employees by salary DESC and compute the cumulative salary and cumulative percentage of the department's total salary expense (rounded to 2). This shows how few top earners make up most of the cost. Return department_name, first_name, last_name, salary, cumulative_salary, cumulative_pct — ordered by department_name, salary DESC, employee_id.

Data you will use

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

employees

  • employee_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • salaryINTEGER
  • department_idINTEGER

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

Hints, when you need them

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

Hint 1

Numerator: a running SUM (explicit ROWS frame). Denominator: the whole-partition SUM (no ORDER BY).

Hint 2

A window SUM with ORDER BY is cumulative; without ORDER BY it is the partition grand total.

Hint 3

The last row in each department always reaches 100%.

Verified SQL answer

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

Reveal solution and explanation
SELECT d.department_name, e.first_name, e.last_name, e.salary, SUM(e.salary) OVER (PARTITION BY e.department_id ORDER BY e.salary DESC, e.employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary, ROUND(SUM(e.salary) OVER (PARTITION BY e.department_id ORDER BY e.salary DESC, e.employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) * 100.0 / SUM(e.salary) OVER (PARTITION BY e.department_id), 2) AS cumulative_pct FROM employees e INNER JOIN departments d ON e.department_id = d.department_id ORDER BY d.department_name, e.salary DESC, e.employee_id;

Why this works

Cumulative-share (Pareto / 80-20) analysis combines two windows over the same partition: a running total and the partition grand total. Their ratio reveals concentration — e.g. how few employees account for most of the payroll.

Expected result

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

department_namefirst_namelast_namesalarycumulative_salarycumulative_pct
EngineeringMichaelScott18000018000025.9
EngineeringDavidMartinez13000031000044.6
EngineeringEmilyChen12500043500062.59
EngineeringRobertWilliams9500053000076.26
EngineeringLisaAnderson9000062000089.21
EngineeringAmandaWhite75000695000100
ExecutiveJenniferKing250000250000100
FinanceJessicaMoore10500010500060
FinanceDanielLee70000175000100
Human ResourcesPatriciaDavis950009500059.38

Previewing 10 of 15 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: