How can you use a window function with DENSE_RANK to find the second highest salary in each department?

To retrieve the second highest salary per department, you can use the DENSE_RANK window function, which assigns the same rank to equal salaries and skips no ranks. The query partitions the data by department_id and orders salaries in descending order. After ranking, you filter rows where the rank equals 2. This approach handles ties correctly: if two employees share the top salary, the next distinct salary receives rank 2, not 3. The query looks like this:

SELECT department_id, salary
FROM (
    SELECT department_id,
           salary,
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2;

The window function runs in O(n log n) time due to sorting, and the overall complexity is dominated by the partitioning and ordering. This method is efficient and portable across most SQL dialects that support window functions.