To solve this problem, we need to identify the top 3 highest salaries in each department, considering all employees who earn any of these top 3 distinct salaries (even if there are ties).

Approach

The key insight here is to use the DENSE_RANK() window function, which:

1. Partitions the data by department to handle each department separately.
2. Orders salaries in descending order to rank the highest salaries first.
3. Assigns consecutive ranks to distinct salaries (unlike RANK(), which skips ranks for ties, or ROW_NUMBER(), which breaks ties arbitrarily).

By filtering rows where the rank is ≤3, we get all employees who earn one of the top 3 distinct salaries in their department.

Solution Code

WITH ranked_employees AS (
    SELECT 
        id, 
        name, 
        department, 
        salary, 
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT id, name, department, salary
FROM ranked_employees
WHERE salary_rank <= 3;

Explanation

1. CTE (Common Table Expression): The ranked_employees CTE computes the rank of each employee's salary within their department using DENSE_RANK().

   • PARTITION BY department: Groups employees by their department.
   • ORDER BY salary DESC: Sorts salaries from highest to lowest.
   • salary_rank: The rank of the salary in the department (consecutive for distinct salaries).

2. Filtering: The main query selects employees from the CTE where the rank is ≤3, ensuring we get all employees with the top 3 distinct salaries in their department.

This approach efficiently handles ties and ensures we include all employees who earn any of the top 3 highest salaries in their department. The use of window functions makes the solution concise and scalable.

作者声明:本文包含人工智能生成内容。