Query with 2 variables beats 99% of submissions (737 ms)


  • 5
    Y

    Note: for each department, if there exists duplicate 1st/2nd/3rd highest salaries, all entries should be returned.

    select d.Name as Department, e.Name as Employee, computed.Salary as Salary
    from Employee e, 
    	(
    		select Salary, DepartmentId, @row := IF(DepartmentId=@did, @row + 1,1) as Rank , @did:=DepartmentId
    		from (
    			select distinct Salary, DepartmentId
    			from Employee
    			order by DepartmentId, Salary desc
    			) ordered, (select @row:=0, @did:=0) variables
    	) computed,
    	Department d
    where e.Salary=computed.Salary 
    and e.DepartmentId=computed.DepartmentId 
    and computed.DepartmentId=d.Id
    and computed.Rank<=3
    order by computed.DepartmentId, Salary desc
    

  • 0
    K

    Noob Question: How did you come to know that this query beats 94% submissions?


  • 1
    Y

    @kinankag once you submit the code, you can check out the Accepted Solutions Runtime Distribution from the acceptance page.


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.