CTEs & Window Functions SQL practice problemHardVerified answer

Running Total: ROWS vs RANGE Frame

Within each department, ordered by salary (which has ties), compute two running totals: one with a ROWS frame and one with a RANGE frame, to expose how they differ on tied values. Return department_name, first_name, last_name, salary, rows_total, range_total — ordered by department_name, salary, employee_id.

  • CTEs
  • Window functions
  • Joins
  • Aggregation
  • Sorting

Interview brief

Understand the request

Within each department, ordered by salary (which has ties), compute two running totals: one with a ROWS frame and one with a RANGE frame, to expose how they differ on tied values. Return department_name, first_name, last_name, salary, rows_total, range_total — ordered by department_name, salary, 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

ROWS = physical row positions. RANGE = logical peer groups (all rows with the same ORDER BY value).

Hint 2

When salaries tie, RANGE gives every tied row the SAME (group-final) subtotal; ROWS gives distinct increasing values.

Hint 3

The default frame when you write ORDER BY without ROWS/RANGE is RANGE — a common source of surprise.

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, e.employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total, SUM(e.salary) OVER (PARTITION BY e.department_id ORDER BY e.salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_total FROM employees e INNER JOIN departments d ON e.department_id = d.department_id ORDER BY d.department_name, e.salary, e.employee_id;

Why this works

ROWS vs RANGE only differ when the ORDER BY column has ties. RANGE treats tied rows as one peer group and assigns them all the group's final cumulative value; ROWS treats each row individually. Knowing this prevents subtle running-total bugs.

Expected result

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

department_namefirst_namelast_namesalaryrows_totalrange_total
EngineeringAmandaWhite750007500075000
EngineeringLisaAnderson90000165000165000
EngineeringRobertWilliams95000260000260000
EngineeringEmilyChen125000385000385000
EngineeringDavidMartinez130000515000515000
EngineeringMichaelScott180000695000695000
ExecutiveJenniferKing250000250000250000
FinanceDanielLee700007000070000
FinanceJessicaMoore105000175000175000
Human ResourcesChristopherWilson650006500065000

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: