SQL Window Ranking Query | Dataford Interview Questions

Your question is SQL Window Ranking Query. 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

OsborneKlein supports compensation analysis for Ameriprise teams. Write a PostgreSQL query that ranks employees by salary within their department and returns employees in the top three salary ranks.

Requirements

Join employees to departments to display the department name.
Use a window function partitioned by department to rank salaries from highest to lowest.
Return every employee whose salary rank is 1, 2, or 3. Tied salaries must share the same rank, so ties may produce more than three employees in a department.
Exclude employees without a matching department and sort the final output by department, salary descending, and employee ID.

Schema

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

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department display name