[LeetCode] Department Top Three Salaries (partition by, rank, sub query)

select b.Name as Department , a.Name as Employee, a.Salary
from (select *, dense_rank() over  (partition by DepartmentId order by salary desc) as sal_rank
from Employee ) a
join Department b
on a.DepartmentId = b.Id
where a.sal_rank <= 3
order by  a.id ;

 

SELECT Name as 'Department', Employee, Salary
    FROM (
        SELECT Salary, Name as 'Employee', DepartmentId, DENSE_RANK() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) as salaryRank 
        FROM Employee) as withSalaryRank
LEFT JOIN Department
ON withSalaryRank.DepartmentId = Department.Id
WHERE salaryRank < 4 
AND Name IS NOT NULL