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. Output every `department` along with its second-highest distinct salary, sorted by `department` name in **descending** order. ### Table Schema | Table | Column | Type | |---|---|---| | `employees` | `employee_id` | integer | | `employees` | `employee_name` | string | | `employees` | `department` | string | | `employees` | `salary` | integer | ### Example Input: `employees` | employee_id | employee_name | department | salary | |---|---|---|---| | 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 | ### Example Output Explanation 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`.
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);