Writing SQL for Ranking | Dataford Interview Questions

Your question is Writing SQL for Ranking. 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

TCS iON is reviewing compensation among its active employees. Write a PostgreSQL query to find every active employee whose salary is the second-highest distinct salary in the employee table.

Requirements

Exclude employees whose employment status is not Active before calculating the salary ranking.
Treat tied salaries as one salary level, so all employees at the second-highest distinct salary must be returned.
Include each employee's name, salary, department name, and calculated salary rank.
Preserve employees even if their department reference is missing, and sort the result by salary descending and employee name ascending.

Schema

employees

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
salary	NUMERIC(12,2)	Annual salary
department_id	INTEGER	Reference to the employee's department
employment_status	VARCHAR(20)	Current employment status

departments

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