Scenario: Helix Systems' HR team is reviewing compensation coverage and needs every employee paired with their current salary alongside their department's total active-salary budget — including employees with no active salary on record.
Task: Write a query to return each employee's id, their department_id, their salary_amount (their current salary, reported as a missing value if no active salary is on record), and dept_salary_total — the combined active salary across employees in their department.
Assumptions:
- An active salary record has
end_daterecorded as a missing value. - An employee's
salary_amountis theamountof their active salary, reported as a missing value when no active salary is on record. - A department's
dept_salary_totalis the combinedamountacross active salaries of employees in that department. - The result covers every employee.
Output:
- One row per employee.
- Columns in this order:
employee_id,department_id,salary_amount,dept_salary_total.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
e.id AS employee_id,
e.department_id,
s.amount AS salary_amount,
SUM(s.amount) OVER (
PARTITION BY
e.department_id
) AS dept_salary_total
FROM
employees e
LEFT JOIN salaries s ON s.employee_id = e.id
AND s.end_date IS NULLThe shape
The LEFT JOIN keeps every employee in the result, and the activity filter sits inside the join condition so an employee with no active salary still gets one row through. The window SUM then computes each department's total across whichever salaries survived the join.
Clause by clause
SELECT e.id AS employee_id, e.department_id, s.amount AS salary_amount, SUM(s.amount) OVER (PARTITION BY e.department_id) AS dept_salary_totalreturns one row per employee with their own salary (NULL when no active salary is on record) and the department-wide total alongside it.FROM employees e LEFT JOIN salaries s ON s.employee_id = e.id AND s.end_date IS NULLpairs each employee with their active salary record. Theend_date IS NULLtest lives inside the join condition, which lets theLEFT JOINretain employees who have no active salary; theirs.amountarrives as NULL.SUM(s.amount) OVER (PARTITION BY e.department_id)computes the per-department total.SUMskips NULL inputs, so an employee with no active salary contributes nothing to the partition total but still appears in the output with the department total on their row.
The trap
Putting s.end_date IS NULL in a WHERE clause instead of inside the join would silently drop every employee whose salary record had a non-null end_date, plus every employee with no salary record at all. The filter belongs in the ON clause so the join preserves the employees the report is supposed to cover.
You practiced computing a per-department total with a window function alongside per-employee detail — without folding rows together, so the output still has one row per employee while carrying the department total inline.
