Second Highest in MySQL | Dataford Interview Questions

Your question is Second Highest in MySQL. 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

Capgemini Engineering is reviewing compensation across its delivery and engineering teams. Write a PostgreSQL query to identify every employee who earns the second highest distinct salary.

Requirements

Rank non-null salaries from highest to lowest using a window function.
Treat duplicate salaries as one salary level, so employees sharing the second highest salary must all be returned.
Include the employee name, department name, and salary.
Preserve employees whose department is missing by using a left join, and order the result by salary descending and employee name ascending.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department_id	INT	Related department identifier
salary	NUMERIC(10,2)	Annual salary

departments

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