Compute a running total of salaries by hire date
Table Employee(id, name, hire_date, salary, dept_id). Return each employee with a running (cumulative) total of salaries ordered by hire_date.
Requirements:
- each row's total = the sum of
salaryfor every employee hired no later than that row - the result is cumulative per row, not a single grand total and not a per-date group sum
- order the output by
hire_date
SELECT id, name, hire_date, salary,
/* running total here */ AS running_total
FROM Employee
-- your query hereWrite the query.
Use a windowed SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The OVER ... ORDER BY makes the aggregate cumulative, and the explicit frame sums every row up to the current one. Add PARTITION BY dept_id for a per-department running total.
- ✗Using
GROUP BY hire_dateand getting per-date sums, not a running total - ✗Omitting
ORDER BYinOVER, which makesSUMtotal the whole partition - ✗Thinking a window frame cannot reference all preceding rows
- →What does
SUM(...) OVER (ORDER BY ...)return without an explicitROWSframe clause? - →How does
PARTITION BY dept_idchange the running total?
Task
Compute a running total of salaries in hire-date order.
SELECT id, name, hire_date, salary,
SUM(salary) OVER (ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS running_total
FROM Employee
ORDER BY hire_date;How it works
This is cumulative aggregation via a window function. The key parts of OVER (...):
ORDER BY hire_dateturns the plainSUMinto a running one: for each row the window spans the rows from the start up to the current one in that order;ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWis the explicit frame: "all rows from the first to the current inclusive." This yields the sum of every salary for employees hired no later than the current row.
Without ORDER BY in OVER, the function would sum the whole partition identically for every row.
⚠️ For a running total within a department, add PARTITION BY dept_id — the total resets at each department boundary.
