Second Maximum Salary Query | Dataford Interview Questions

Your question is Second Maximum 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

HashedIn's engineering dashboard needs to identify active employees whose compensation is the second-highest distinct salary in the company. Write a PostgreSQL query that also displays each employee's department when available.

Requirements

Consider only employees where is_active is TRUE and salary is not NULL.
Use a ranking technique that treats tied salaries as one salary level.
Return every employee at the second-highest distinct salary, including employees whose department reference has no match.
Sort the output by employee_id 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 employee salary
department_id	INTEGER	Referenced department identifier
is_active	BOOLEAN	Whether the employee is currently active

departments

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