Scenario: Helix Systems' compensation committee is comparing each employee's current salary to their department's average and needs every employee included — even employees with no active salary on record.
Task: Write a query to return each employee's id, name, salary_amount (their current salary, reported as a missing value if no active salary is on record), and dept_avg_salary (the average current 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_avg_salaryis the average of every active salary across employees in that department. - The result covers every employee.
Output:
- One row per employee.
- Columns in this order:
employee_id,employee_name,salary_amount,dept_avg_salary. - Sorted by
department_idascending, thenemployee_idascending.
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.name AS employee_name,
s.amount AS salary_amount,
AVG(s.amount) OVER (
PARTITION BY
e.department_id
) AS dept_avg_salary
FROM
employees e
LEFT JOIN salaries s ON s.employee_id = e.id
AND s.end_date IS NULL
ORDER BY
e.department_id,
e.idThe shape
The LEFT JOIN keeps every employee in the result, and AVG(s.amount) OVER (PARTITION BY e.department_id) puts the department's benchmark on each row without folding rows together. For an employee with no active salary, the join produces one row with s.amount as NULL, and AVG ignores that NULL when computing the partition average — so the benchmark stays clean.
Clause by clause
SELECT e.id AS employee_id, e.name AS employee_name, s.amount AS salary_amount, AVG(s.amount) OVER (PARTITION BY e.department_id) AS dept_avg_salaryreturns one row per employee with both their own salary and the department average on the same row.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. Theend_date IS NULLtest inside theONclause both selects the active record and lets unmatched employees survive — theirs.amountarrives as NULL.AVG(s.amount) OVER (PARTITION BY e.department_id)computes each department's average across the salaries that the join actually returned.AVGskips NULL inputs, so employees with no active salary do not lower the average toward zero — they simply don't participate in it.ORDER BY e.department_id, e.idsorts the report by department, then by employee within each department.
The trap
An employee with no active salary correctly shows salary_amount as NULL, but their row still carries the department's dept_avg_salary. That happens because the window function runs per partition row, not per non-NULL row. The unmatched employee is still a row in the Engineering partition, so they still get Engineering's average alongside their own NULL salary. That is the intended behavior here, but it is easy to misread as a propagation bug.
You practiced computing a per-department average as a window function over the per-employee detail, so every employee carries their department benchmark inline regardless of whether their own salary is on record.
