SQL Quest › SQL Interview Questions › Window Functions
Salary vs Department Average (PARTITION BY)
Comparing everyone to the company average was too blunt — a Sales rep and an Engineer aren't on the same scale. HR wants each employee measured against their own department's average.
Show name, department, salary, dept_avg (their department's average salary, rounded to 2 decimals) and diff_from_dept_avg (salary minus that average, rounded to 2 decimals). Order by department, then salary descending, then name.
The new idea is PARTITION BY: it splits the table into groups and restarts the window in each one. AVG(salary) OVER (PARTITION BY department) gives each row its own department's average, not the company's. This is the difference between a window function and GROUP BY in one line — you still get all 50 rows, but the aggregate is now per-group.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
employees
| emp_id | name | department | position | salary | hire_date | manager_id | performance_rating |
|---|---|---|---|---|---|---|---|
| 1 | Alice Johnson | Engineering | Senior Developer | 95000 | 2019-03-15 | 5 | 4.5 |
| 2 | Bob Smith | Engineering | Developer | 75000 | 2020-06-01 | 1 | 3.8 |
| 3 | Carol Williams | Marketing | Marketing Manager | 85000 | 2018-09-20 | NULL | 4.2 |
Expected output: dept_avg = 87055.56, diff_from_dept_avg = 27944.44
Hint
Concepts
SELECT Window Functions PARTITION BY AVG
Practise the topic: SQL practice questions · Window function practice · GROUP BY exercises
In these company practice sets
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test