How can you use a window function to retrieve the second highest salary per department?

To retrieve the second highest salary per department, you can use the RANK() window function. RANK() assigns the same rank to identical values, so if two employees tie for first place, the next rank will be 3, ensuring that the true second highest salary is captured. The query looks like this: SELECT department, salary FROM ( SELECT department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk FROM employees ) sub WHERE rnk = 2; This returns one row per department with the second highest salary. If you want exactly one row per department even when there are ties, you could use ROW_NUMBER() instead, but that would skip ties. Complexity is O(n log n) due to the sort performed by the window function. RANK() is preferred when you need to preserve ties, whereas ROW_NUMBER() gives a deterministic ordering. In practice, you might also add a LIMIT clause or use QUALIFY in Snowflake to filter the rank directly. If you are using PostgreSQL, you can also use the DISTINCT ON clause to achieve a similar result, but the window function approach is more portable across SQL dialects. Remember to index the salary column if the table is large, as the window function will need to sort the data. Also, consider using a CTE for readability: WITH ranked AS ( SELECT department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk FROM employees ) SELECT department, salary FROM ranked WHERE rnk = 2; This keeps the logic clear and easy to maintain.