Each salary against the department best

SQLHard12 min

Your task

For each employee with a recorded salary, return name, salary, and gap_to_top: how far their salary sits below the highest in their own department. Order by name.

Tables

department
idINTEGERnameTEXT
1Engineering
2Sales
3Design
4Research
employee
idINTEGERnameTEXTsalaryINTEGERdepartment_idINTEGERmanager_idINTEGERhired_onTEXT
1Ada1800001NULL2021-03-01
2Grace165000112022-06-15
3Linus140000112023-01-09
4Mel1200002NULL2020-11-20
5Kay120000242023-04-02
6IvanNULL242024-02-17
More rows not shown