Nth Highest Salary Query | Dataford Interview Questions

Your question is Nth Highest Salary 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

Freecharge’s people analytics team needs to identify employees at a selected salary tier. Write a PostgreSQL query to return every active employee earning the third-highest distinct salary.

Requirements

Consider only employees whose employment_status is ACTIVE and whose salary is not NULL.
Use DENSE_RANK() so employees with the same salary share a rank, and return rank 3.
Include the employee name, salary, salary rank, and department name. Preserve employees whose department is missing or unmatched by using a LEFT JOIN.
Order the result by salary descending and employee ID ascending.

Schema

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 employee salary
employment_status	VARCHAR(20)	Current employment status

departments

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