Show each employee's salary next to their department's average on the same row
Table employees(employee_id, department, salary). For each employee return their salary and the average salary of their department, both on the same row. Every employee must appear — do not collapse the result to one row per department.
-- one row per employee: salary and their department's average salaryWrite the query.
Use a window aggregate, not GROUP BY: AVG(salary) OVER (PARTITION BY department). The partition scopes the average per department, yet returns a value for every row, so each salary sits beside its department average.
- ✗Reaching for
GROUP BY, which collapses the employee rows - ✗Returning one row per department instead of per employee
- ✗Omitting
PARTITION BY, giving the global average instead of per-department
- →How would you also flag employees paid above their department average?
- →What does
OVER ()with no partition compute here?
The window aggregate computes the department average yet, unlike GROUP BY, preserves every employee row:
SELECT employee_id,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;PARTITION BY department scopes the window to each department while the function returns a value per row. GROUP BY department would be wrong here — it collapses a department's employees into one row. An empty OVER () window would give one global average over the whole table, not a per-department one.
