Company: Github | Difficulty: medium
GitHub's People Analytics team is auditing compensation bands and wants to know the second-highest distinct salary within each department, to compare it against the top salary and flag unusually wide gaps.
Write a query to find the second-highest distinct salary in each department. If two or more employees in a department are tied for the highest salary, the second-highest distinct salary is the next different salary value below that (not a duplicate of the top salary). If a department does not have a second distinct salary value (e.g., everyone in the department earns the same amount, or the department has only one employee), output NULL for that department instead of omitting the department entirely.
department
NULL
Output every department along with its second-highest distinct salary, sorted by department name in descending order.
employees
employee_id
employee_name
salary
In Engineering, two employees (Dana and Priya) are tied for the highest salary at 120,000. The next distinct salary value below that is 95,000 (Leo's), so Engineering's second-highest salary is 95,000. In Design, both employees earn the exact same salary (88,000), so there is no second distinct salary value, and Design outputs NULL.
Engineering
Design
DROP TABLE IF EXISTS employees; CREATE TABLE employees (employee_id INT, employee_name VARCHAR(255), department VARCHAR(100), salary INT); INSERT INTO employees VALUES (1, 'Dana Cho', 'Engineering', 120000), (2, 'Priya Nair', 'Engineering', 120000), (3, 'Leo Marsh', 'Engineering', 95000), (4, 'Ravi Patel', 'Design', 88000), (5, 'Tomas Berg', 'Design', 88000);
← All SQL Challenges · Github Questions · Learn SQL Free