Scenario: Helix Systems' finance team needs a department salary report showing each department's name, its current salary expenditure, and its share of the company-wide current salary expenditure.
Task: Write a query to return each department_name, its dept_total (combined current salary), and its salary_share (its share of the company-wide current salary expenditure expressed as a percentage).
Assumptions:
- A current salary record has
end_daterecorded as a missing value. - A department's
dept_totalis the combined current salaryamountacross its employees. - A department's
salary_shareisdept_totaldivided by the combined current salary across every department in the result, multiplied by100. - The result covers only departments with at least one current salary record.
Output:
- One row per qualifying department.
- Columns in this order:
department_name,dept_total,salary_share.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
WITH
dept_salary_totals AS (
SELECT
e.department_id,
SUM(s.amount) AS dept_total
FROM
employees e
JOIN salaries s ON s.employee_id = e.id
WHERE
s.end_date IS NULL
GROUP BY
e.department_id
),
dept_shares AS (
SELECT
department_id,
dept_total,
dept_total * 100.0 / SUM(dept_total) OVER () AS salary_share
FROM
dept_salary_totals
)
SELECT
d.name AS department_name,
ds.dept_total,
ds.salary_share
FROM
dept_shares ds
JOIN departments d ON d.id = ds.department_idThe shape
Two CTEs and a window function carry the work. The first CTE totals current salaries by department; the second uses SUM(dept_total) OVER () to attach the company-wide total to every department row in one pass. The outer query joins to departments for the name.
Clause by clause
WITH dept_salary_totals AS (SELECT e.department_id, SUM(s.amount) AS dept_total FROM employees e JOIN salaries s ON s.employee_id = e.id WHERE s.end_date IS NULL GROUP BY e.department_id)produces one row per department that has at least one current salary record. TheWHERE s.end_date IS NULLfilter restricts to current salaries before the aggregation, and the innerJOINexcludes departments with no qualifying record — matching the prompt's contract.dept_shares AS (SELECT department_id, dept_total, dept_total * 100.0 / SUM(dept_total) OVER () AS salary_share FROM dept_salary_totals)is where the window function does its work.SUM(dept_total) OVER ()with an empty window frame sumsdept_totalacross every row indept_salary_totalsand attaches that single value to each row. The division then computes each department's share against the same denominator.SELECT d.name AS department_name, ds.dept_total, ds.salary_share FROM dept_shares ds JOIN departments d ON d.id = ds.department_idjoins the department name in. The innerJOINis safe because every row indept_sharescame from a department that has a current salary; the join just attaches the label.
Why SUM(...) OVER () and not a scalar subquery
SELECT department_id, dept_total, dept_total * 100.0 / (SELECT SUM(dept_total) FROM dept_salary_totals) AS salary_share FROM dept_salary_totals produces the same numbers. The window form pulls the company-wide total into the same scan rather than running a separate aggregation pass. On a small department table the difference is irrelevant; the readability is comparable; pick either.
The trap
The denominator is "the combined current salary across every department in the result," not "across every salary record." Those are the same number here because the CTE is built only from current salaries, but the wording is what matters if anything ever filters dept_shares further. SUM(dept_total) OVER () always sums across every row of the CTE before any outer filter — that is what makes the shares add to exactly 100 here. A common related mistake is writing 100 instead of 100.0 in the multiplier: integer arithmetic would truncate any fractional percentage to a whole-number share, and most of the rows would end up as 0 or low-precision integers instead of the intended decimals.
You practiced layering two CTEs — first per-department totals, then a window-function denominator over them — before pairing each department with its name from a third source.
