SQL - Department Top Three Salaries

Department Top Three Salaries

Hard

SQL Schema

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

 

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).

 

Explanation:

In IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.

 

 

ANALYSIS:

the question requests to query the top 3 salaries by each department. The first thing that I need to do is *rank* each employees based on their salaries. Yet, to get the tied salaries i.e. Randy and Joe (85000), I need to use *dense rank* instead of *rank*.

 

The difference between *dense rank* and *rank* is rank does not consider ties and keep on ranking them, while dense rank consider ties and provides same rank for the ties. 

 

After forming the dense rank, I need to join the employee table with department table to get the right column names.

 

Lastly, I need to make a condition rank <=3) in WHERE to query the top 3 salaries.  

SELECT 
    d.Name as Department, temp.Name as Employee, Salary
FROM
    (SELECT 
        DepartmentId, Name, Salary,
        @rank :=
            CASE 
            -- this could be done by dense_rank()over(partition by DepartmentID order Salary DESC)
                WHEN @dept = DepartmentId AND @sal = Salary THEN @rank 
                WHEN @dept = DepartmentId AND @sal > Salary THEN @rank + 1
                ELSE 1
            END as rank, 
        @dept := DepartmentId, 
        @sal := Salary
    FROM 
        Employee, 
        (SELECT @rank :=0, @dept := null, @sal := null) f
    ORDER BY 
        DepartmentId, Salary DESC) temp
        
        JOIN Department d ON temp.DepartmentId = d.Id
WHERE 
    rank <= 3