CTEs & Window Functions SQL practice problemEasyVerified answer

Running Total of Salaries Per Department (Explicit Frame)

Return each employee with a running total of salary within their department.

  • CTEs
  • Window functions
  • Joins
  • Aggregation
  • Sorting

Interview brief

Understand the request

Finance operations analyst The cumulative payroll report shows a running total of salaries across employees, reset per department.

Within each department, compute a running total of salary ordered by hire_date (ties broken by employee_id). Use an explicit ROWS frame. Return first_name, last_name, hire_date, salary, department_name, running_total — ordered by department_name, hire_date, employee_id.

Return

  • Return employee_name, department_id, salary, and running_total.

Constraints

  • Use SUM() OVER (PARTITION BY department_id ORDER BY salary) as a window function.

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)
  • hire_dateDATE
  • 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

PARTITION BY restarts the running total for each department.

Hint 2

Without an explicit frame, the default is RANGE — which sums ALL tied rows together. ROWS avoids that surprise.

Hint 3

Always add a tie-breaker (employee_id) in the window ORDER BY for a deterministic running total.

Verified SQL answer

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

Reveal solution and explanation
SELECT e.first_name, e.last_name, e.hire_date, e.salary, d.department_name, SUM(e.salary) OVER (PARTITION BY e.department_id ORDER BY e.hire_date, e.employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM employees e INNER JOIN departments d ON e.department_id = d.department_id ORDER BY d.department_name, e.hire_date, e.employee_id;

Why this works

Running totals are windowed SUMs with a 'start-to-here' frame. Spelling out ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW removes the RANGE-vs-ROWS ambiguity that bites people when the ORDER BY column has ties (Q16 explores this).

Expected result

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

first_namelast_namehire_datesalarydepartment_namerunning_total
MichaelScott2019-03-20180000Engineering180000
DavidMartinez2020-02-14130000Engineering310000
EmilyChen2020-05-18125000Engineering435000
RobertWilliams2021-01-1095000Engineering530000
LisaAnderson2021-03-2290000Engineering620000
AmandaWhite2022-03-1075000Engineering695000
JenniferKing2018-01-15250000Executive250000
JessicaMoore2020-04-08105000Finance105000
DanielLee2021-07-1570000Finance175000
PatriciaDavis2019-11-0595000Human Resources95000

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: