Second-Highest Salary SQL

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

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

Output details

• Return columns in this order: department_id, department_name, employee_count, active_employee_count, average_salary, second_highest_salary, employees_at_second_highest.
• Count active employees only when employment_status equals active.
• Report average_salary as NULL for departments with no employees, while employee counts remain 0.
• Repeat the same second_highest_salary and employees_at_second_highest values on every department row.