Department High Earners (Top Three Salaries) | Dataford Interview Questions

Your question is Department High Earners (Top Three Salaries). 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

TCS iON workforce reporting needs a query that identifies high-earning employees within every department. An employee is a high earner when their salary belongs to one of the department's top three unique, non-NULL salary values.

Write a PostgreSQL query that returns all qualifying employees, including every employee tied at one of those salary levels.

Requirements

Join employees to their department names.
Rank distinct salary levels independently within each department, with the highest salary ranked first.
Return all employees whose salary rank is 1, 2, or 3. Do not return employees with NULL salaries or employees without a valid department.
Include departments with fewer than three salary levels by returning every available qualifying level.
Order the output by department name, salary rank, salary descending, and employee ID.

Schema

departments

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

employees

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department_id	INTEGER	Employee's department identifier
salary	NUMERIC(12,2)	Annual employee salary