SQL query to find the second highest salary from each department.

The most robust SQL approach to find the second highest salary from each department utilizes a window function, specifically DENSE_RANK(), to assign a rank within each department based on salary, then filters for rank 2.

SELECT
    dept_id,
    salary
FROM (
    SELECT
        dept_id,
        salary,
        DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
    FROM
        employee
) AS ranked_salaries
WHERE
    rk = 2;

Mechanics and Why It Works

This query employs a subquery (or a Common Table Expression) to first calculate the rank of each employee's salary within their respective department.
PARTITION BY dept_id: This clause divides the dataset into independent groups, one for each dept_id. All subsequent window function calculations (like ranking) are performed within* these partitions, ensuring per-department analysis.
* ORDER BY salary DESC: Within each department partition, employees are ordered by their salary in descending order. This ensures that the highest salary receives rank 1.
DENSE_RANK(): This window function assigns a unique rank to each distinct salary value within a partition. If multiple employees share the highest salary, they all receive rank 1, and the next distinct* salary receives rank 2. This is crucial for "Nth highest" problems with ties; RANK() would assign rank 1 to all tied highest salaries but then skip rank 2, assigning 3 to the next distinct salary. ROW_NUMBER() would assign a unique rank to each row, even for tied salaries, which doesn't align with finding the "second highest salary tier."
* WHERE rk = 2: The outer query then filters these ranked results, selecting only those rows where the calculated rank is exactly 2, effectively giving us the second highest salary (or salaries, if tied) for each department.

Key Trade-offs and Considerations

Using window functions like DENSE_RANK() is generally the most efficient and readable SQL solution for this problem. Databases optimize these operations, often involving an internal sort per partition. For very large datasets in distributed systems (e.g., Apache Spark, Snowflake), the PARTITION BY clause can lead to data shuffling, where all data for a given dept_id must be co-located on the same processing unit. This network I/O can be a significant performance factor, especially with high data skew (one department having vastly more employees).

This solution gracefully handles edge cases: departments with fewer than two employees will simply not appear in the final result, and it correctly identifies all employees who share the second highest salary if there are ties.

In the interview, also mention…

Discuss the critical distinction between RANK(), DENSE_RANK(), and ROW_NUMBER() and why DENSE_RANK() is the most appropriate for "Nth highest" questions involving potential ties. Also, touch upon the performance characteristics of window functions, especially data shuffling in distributed environments due to PARTITION BY clauses.