Second-Last Salary Employee | Dataford Interview Questions

Your question is Second-Last Salary Employee. 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

Quantiphi's employee analytics team needs to identify employees whose current salary is the second-highest distinct salary across the organization. Write a PostgreSQL query that returns every employee tied at that salary and includes the department name when available.

Requirements

Exclude employees whose salary is NULL from salary ranking.
Rank distinct salary levels from highest to lowest, preserving ties.
Return every employee with rank 2, including employee ID, name, salary, and department name.
Use a LEFT JOIN so employees remain eligible even if their department is missing or unknown, and order results by salary descending and employee ID ascending.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
first_name	VARCHAR(50)	Employee first name
last_name	VARCHAR(50)	Employee last name
salary	NUMERIC(12,2)	Current annual salary
department_id	INT	Optional reference to the employee department

departments

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