SQL Highest Salary Per Department | Dataford Interview Questions

Turing's engineering operations team needs a department-level view of the highest employee compensation. Write a PostgreSQL query that reports the employees earning the maximum salary in each department.

Requirements

Include every department, including departments with no employees.
Return the department name, employee name, and highest salary.
Include every employee tied for the highest salary in a department.
Sort by department name, then employee name, with departments without employees listed first within their department.

A NULL salary should not be treated as a department's maximum salary. Departments with no qualifying employee should still appear with NULL employee and salary values.

Schema

departments

Column	Type	Description
department_idPK	INT	Primary key for the department
department_name	VARCHAR(100)	Department name

employees

Column	Type	Description
employee_idPK	INT	Primary key for the employee
employee_name	VARCHAR(100)	Employee's full name
department_id	INT	Referenced department, when assigned
salary	NUMERIC(12,2)	Annual salary

Your question is SQL Highest Salary Per Department. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem