SQL Nth Highest Per Group | Dataford Interview Questions

Your question is SQL Nth Highest Per Group. 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

TIAA's workforce analytics team needs to compare compensation levels across departments. Write a PostgreSQL query that returns every employee earning the second-highest distinct salary in their department.

Use N = 2 for this exercise. Treat equal salaries as the same rank, so employees tied at the second salary level must all be returned. Departments without a second salary level should not appear.

Requirements

Join employees to departments using department_id.
Rank non-null salaries within each department using a window function, with the highest distinct salary ranked first.
Return the employee name, department name, salary, and salary rank for rank 2, ordered by department name and employee name.

Schema

departments

Column	Type	Description
department_idPK	INTEGER	Unique department identifier
department_name	VARCHAR(100)	TIAA department name

employees

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