SQL Second Highest Salary | Dataford Interview Questions

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

HCLTech's workforce analytics team needs to identify employees earning the second-highest distinct salary among active employees assigned to a valid department. Write a PostgreSQL query that returns every employee tied at that salary.

Requirements

Consider only employees whose employment_status is Active and whose salary is not NULL.
Join employees to their valid department records.
Use a ranking technique to find the second-highest distinct salary, preserving ties.
Return the employee ID, employee name, department name, and salary, ordered by employee ID.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department_id	INT	Referenced department identifier
salary	NUMERIC(12,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)	HCLTech department name