SQL Rank Within Departments | Dataford Interview Questions

Your question is SQL Rank Within Departments. 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

Munich Reinsurance America needs a workforce report identifying employees whose salary is at rank 2 within their department. Write a PostgreSQL query that joins employee records to department names and ranks salaries independently for each department.

Requirements

Use RANK() partitioned by department, ordering salaries from highest to lowest.
Return every employee whose salary has rank 2. Employees tied at the second salary level must all be included.
Exclude employees without a matching department and order the results by department name, salary descending, and employee name.

Schema

departments

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

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department_id	INT	Referenced department identifier
salary	NUMERIC(12,2)	Annual salary in US dollars