SQL Quest › SQL Interview Questions › Window Functions

Every Employee Next to Their Department's Top Salary

EasyFreeQuerying BasicsWindow FunctionsAggregation & Grouping

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_idnamedepartmentpositionsalaryhire_datemanager_idperformance_rating
1Alice JohnsonEngineeringSenior Developer950002019-03-1554.5
2Bob SmithEngineeringDeveloper750002020-06-0113.8
3Carol WilliamsMarketingMarketing Manager850002018-09-20NULL4.2

Expected output: 50 rows — every Engineering row carries 115000, every HR row 80000

Hint

You need a per-department figure on every row without collapsing the rows into one per department. A window can do that: take the maximum over the employees of the same department only. Then subtract the employee's own salary from it.

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

Your First Window FunctionEasy · FreeEach Salary's Share of PayrollEasy · FreeRank Everyone by SalaryEasy · FreeNumber Each Customer's OrdersEasy · FreeWindow Functions: ROW_NUMBERMedium · FreeRANK vs DENSE_RANK Side-by-SideMedium · Free

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