Second Highest Without Aggregates | Dataford Interview Questions

AvidXchange wants to review compensation bands by department. Write a PostgreSQL query to find the second highest distinct salary in each department, without using any aggregate functions such as MAX() or COUNT().

Requirements

Return one row per department that has at least two distinct salaries.
Find the second highest distinct salary within each department.
Do not use aggregate functions in the solution.
Sort the final result by department_name.

Output details

Return columns in this order: department_name, then second_highest_salary.
Sort by department_name in ascending order.
Exclude rows where salary is NULL.
Preserve salary values without rounding or numeric conversion.

Schema

employees

Column	Type	Description
employee_idPK	INT	Primary key for the employee
employee_name	VARCHAR(100)	Employee name
department_name	VARCHAR(100)	Department name
salary	INT	Employee salary, may repeat across employees
hire_date	DATE	Employee hire date

Your question is Second Highest Without Aggregates. Start with the requirements and the one table 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