UST SQL: Second Maximum Salary | Dataford Interview Questions

Your question is UST SQL: Second Maximum Salary. 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

UST's payroll reporting team needs to identify employees earning the second-highest distinct salary. Write a PostgreSQL query that returns every employee tied at that salary and includes the department name when available.

Requirements

Rank distinct salary values from highest to lowest, ignoring ties when determining the second-highest value.
Return the employee ID, employee name, salary, and department name for every employee at rank two.
Preserve qualifying employees even when their department is missing, and sort results 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
salary	NUMERIC(12,2)	Annual employee salary
department_id	INT	References departments.department_id

departments

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