Second Highest Salary per Department
Hard · Window Functions
DENSE_RANK() numbers rows by a sort order, gives tied rows the same rank, and leaves no gaps: salaries 9000, 8000, 8000, 7000 get ranks 1, 2, 2, 3.
For each department, find the employees who earn the second highest distinct salary in that department. If several people share that salary, return all of them. Departments with fewer than two distinct salaries return nothing. Ignore employees without a department.
Return department (department name), employee (employee name) and salary, sorted by department, then employee.