Third-Highest Salary Department-Wise | Dataford Interview Questions

Context

Straive uses department-level compensation reports to review salary distribution across teams supporting its content and data platforms. The report must identify employees whose salary is the third-highest distinct salary within their department.

Task

Write a PostgreSQL query that returns every employee tied at the third-highest distinct salary in departments with at least three distinct salary levels.

Requirements

Join employees to departments using department_id.
Rank distinct salary values independently within each department, with the highest salary ranked first.
Return the department name, employee ID, employee name, salary, and salary rank for rank 3.
Include ties and order the output by department name and employee ID.

Schema

departments

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

employees

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department_id	INTEGER	Department assigned to the employee
salary	NUMERIC(10,2)	Annual employee salary

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