SQL Max Salary Per Department | Dataford Interview Questions

Your question is SQL Max Salary Per Department. Start with the requirements and the two tables on the right.

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

Problem

OpenText wants a compensation report showing the highest-paid employees in each department. Write a PostgreSQL query that returns every employee tied for the maximum non-null salary in their department.

Requirements

Use a window function or equivalent ranking logic to identify the maximum salary per department.
Return all employees sharing that maximum salary, not just one employee.
Include the department name, employee name, and salary.
Exclude employees without a department or salary, and sort by department name followed by employee name.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Name of the department

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department_id	INT	References departments.department_id
salary	NUMERIC(12,2)	Annual employee salary