Top Salaries by Department in Oracle | Dataford Interview Questions

The Plymouth Rock People Operations team needs a department-level view of its highest-paid employees. Write a PostgreSQL query that returns the top five salaries within each department.

Requirements

Join employees to departments using department_id.
Rank employees separately within each department by salary from highest to lowest.
Return no more than five employees per department, using employee_id as the deterministic tie-breaker.
Sort the final results by department name and salary rank. Employees with NULL salaries should not appear in the top five when a department has at least five non-NULL salaries.

Schema

departments

Column	Type	Description
department_idPK	INTEGER	Primary key for the department
department_name	VARCHAR(100)	Name of the Plymouth Rock department

employees

Column	Type	Description
employee_idPK	INTEGER	Primary key for the employee
employee_name	VARCHAR(100)	Employee's full name
department_id	INTEGER	References departments.department_id
salary	NUMERIC(10,2)	Annual salary in dollars

Your question is Top Salaries by Department in Oracle. 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