Second-Highest Salary SQL | Dataford Interview Questions

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

Airtel engineering leadership wants a salary benchmark alongside department-level workforce statistics. Write a PostgreSQL query that identifies the second-highest distinct salary and returns it with aggregated statistics for every Airtel department.

Requirements

Use a window function to find the second-highest distinct non-NULL salary and count how many employees earn it.
Return every department, including departments with no employees, using a LEFT JOIN.
For each department, calculate total employees, active employees, and average salary rounded to two decimal places.
Order the result by department_id ascending.

Schema

departments

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

employees

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
salary	NUMERIC(12,2)	Annual employee salary
department_id	INTEGER	Referenced department identifier
employment_status	VARCHAR(20)	Current employment status