Department-Wise Second Highest Salary | Dataford Interview Questions

Incedo's workforce analytics team needs to identify the second-highest salary in each department. Write a PostgreSQL query using department and employee data.

Requirements

Return every employee whose salary is the second-highest distinct salary in their department.
Treat tied salaries as the same salary rank and return all employees sharing rank 2.
Exclude employees whose salary is NULL and departments with fewer than two distinct non-null salaries.
Return results ordered by department name and employee name.

Schema

departments

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

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee name
department_id	INT	References departments.department_id
salary	NUMERIC(12,2)	Annual salary

Your question is Department-Wise Second Highest Salary. 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