Max Salary Per Department | Dataford Interview Questions

HID WorkforceID needs a salary review showing the highest-paid employee or employees in each department. Write a PostgreSQL query that returns all employees tied for the maximum salary within their department.

Requirements

Join employees to departments and return the department name, employee name, and salary.
Use a window function to rank employees within each department by salary in descending order.
Return every employee tied for the highest non-null salary. Exclude employees whose salary is NULL.
Sort the result by department name and employee name.

Schema

departments

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

employees

Column	Type	Description
employee_idPK	INT	Primary key for the employee
employee_name	VARCHAR(100)	Employee's full name
department_id	INT	References departments.department_id
job_title	VARCHAR(100)	Employee's job title
salary	NUMERIC(12,2)	Annual salary

Your question is Max 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