Leetcode Department Top Three Salary problem solution

YASH PAL,

In this Leetcode Department Top Three Salary problem solution, A company’s executives are interested in seeing who earns the most money in each of the company’s departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department. Write an SQL query to find the employees who are high earners in each of the departments. Return the result table in any order.

Leetcode Department Top Three Salary problem solution

Problem solution in Oracle.

SELECT DEPARTMENT,EMPLOYEE,SALARY
FROM (SELECT D.NAME DEPARTMENT,E.NAME EMPLOYEE,E.SALARY SALARY,
       DENSE_RANK() OVER(PARTITION BY D.ID ORDER BY E.SALARY DESC) RANK
FROM EMPLOYEE E 
     INNER JOIN 
     DEPARTMENT D
ON E.DEPARTMENTID = D.ID)
WHERE RANK <= 3

Problem solution in Mysql.

select department,employee,salary
from
(select d.name as department,e.name as employee,e.salary,
dense_rank() over(partition by departmentid order by salary desc)rm from employee e join department d on e.departmentid=d.id)a
where rm <4

Problem solution in SQL server.

SELECT t.Department, t.Employee, t.Salary FROM ( 
SELECT d.Name As Department, e.Name As Employee, e.Salary, DENSE_RANK() OVER(PARTITION BY e.DepartmentId Order By e.Salary DESC) As row
FROM Employee e INNER Join Department d
    ON e.DepartmentID = d.Id) As t
    Where t.row <=3

coding problems