SQL for Ranked Retrieval | Dataford Interview Questions

HashedIn by Deloitte's workforce reporting needs to identify employees earning the second-highest salary among currently active employees. Write a PostgreSQL query that returns employee details and their department information.

Requirements

Consider only employees whose employment_status is Active and whose salary is not NULL.
Return every employee tied at the second-highest distinct salary.
Include employees without a department assignment, showing NULL for the department name.
Sort the output by employee_id in ascending order.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department_id	INT	Assigned department identifier
salary	NUMERIC(10,2)	Annual employee salary
employment_status	VARCHAR(20)	Current employment status

departments

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

Your question is SQL for Ranked Retrieval. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem