Second Largest Salary Query | Dataford Interview Questions

Capgemini Government Solutions needs a compensation report for employees assigned to current government delivery programs. Write a PostgreSQL query to find employees whose salary is the second-highest distinct salary among eligible employees.

Requirements

Join employees to employee_assignments using employee_id.
Include only employees with employment_status = 'Active' and a current assignment.
Rank distinct salary levels from highest to lowest, and return every employee at rank 2. Exclude NULL salaries.
Return employee_id, employee_name, and salary, ordered by salary descending and employee ID ascending.

Schema

employees

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
salary	NUMERIC(10,2)	Annual employee salary
employment_status	VARCHAR(20)	Employment status

employee_assignments

Column	Type	Description
assignment_idPK	INTEGER	Unique assignment identifier
employee_id	INTEGER	Employee assigned to the program
program_name	VARCHAR(120)	Government delivery program name
is_current	BOOLEAN	Whether the assignment is currently active

Your question is Second Largest Salary Query. 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