SQL Quest › SQL Interview Questions › Window Functions
Every Employee Next to Their Department's Top Salary
Before pay reviews, each manager wants a roster where every employee sits next to the highest salary in their own department, plus how far below it they are. Nobody should disappear from the list: all 50 employees stay, one row each.
Show exactly these 5 columns, in this order: name, department, salary, dept_top_salary (the highest salary in that employee's department) and gap (dept_top_salary minus salary, so the top earner shows 0). Order by department, then salary from highest to lowest, then name.
The top salary must be the department's own, not the company's: an HR employee is compared with HR's best-paid, not with Engineering's.
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". Free accounts get 10 free challenge solves, and this can be one of them.
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: 50 rows — every Engineering row carries 115000, every HR row 80000
Hint
Concepts
SELECT Window Functions MAX PARTITION BY
Practise the topic: SQL practice questions · Window function practice · GROUP BY exercises
Read the concept: SQL running total
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