365 Days of Coding: Day 17

Today I solved a hard SQL leetcode challenge. I failed in one of the test case and I can’t seem to explain the reason why it failed.

Challenge: Give a table called ‘Employee’ and ‘Department’, I had to find out top 3 highest paying employees in each department.

Table: Employee

Id	Name	Salary	DepartmentId
1	Joe	85000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1
7	Will	70000	1

Table: Department

Id	Name
1	IT
2	Sales

Expected Output:

Department	Employee	Salary
IT	Max	90000
IT	Randy	85000
IT	Joe	85000
IT	Will	70000
Sales	Henry	80000
Sales	Sam	60000

My Solution:

SELECT Department, Employee, Salary
FROM (
    SELECT Department, Employee, Salary, 
    ROW_NUMBER () OVER (Partition by Department 
    Order By Salary desc) As rowk 
    FROM (
        Select d.Name AS Department, e.Name AS Employee, 
        e.Salary AS Salary 
        FROM Employee e 
        LEFT JOIN Department d 
        ON e.DepartmentID = d.Id
        ) )
WHERE rowk < 4

My solution failed in one of the test case.

Test Case: The ‘Employee’ table had only one record and the ‘Department’ table had no records.

Table: Employee

Id	Name	Salary	Departent Id
1	Joe	10000	1

Table: Department

The Department table for the test case is NULL.

Expected Output From The Test Case

NULL

My above solution gave the following output.

Department	Employee	Salary
null	Joe	1000

After looking at the other solution, I realised that the join should be an ‘INNER JOIN’ rather than a ‘LEFT JOIN’ to pass the test case.

My Revised Solution:

SELECT Department, Employee, Salary
FROM (
    SELECT Department, Employee, Salary, 
    DENSE_RANK () OVER (Partition by Department 
    Order By Salary desc) As rowk 
    FROM (
        Select d.Name AS Department, e.Name AS Employee, 
        e.Salary AS Salary 
        FROM Employee e 
        JOIN Department d 
        ON e.DepartmentID = d.Id
        ) )
WHERE rowk < 4

Other Submitted Solution From the Forums:

Solution 1

WITH agg AS (
SELECT DepartmentId, Name, Salary, DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS rank
FROM Employee)

SELECT d.Name AS "Department", a.Name AS "Employee", a.Salary AS "Salary"
FROM agg a 
JOIN Department d ON a.DepartmentId = d.Id
WHERE a.rank < 4
Runtime: 1053 ms

Solution 2

SELECT Department, Employee, Salary
FROM (
    SELECT b.name as Department, a.name as Employee, a.salary,
    DENSE_RANK() 
    OVER(PARTITION BY b.name ORDER BY a.salary DESC) AS rn
    FROM Employee a
        JOIN Department b
        ON a.departmentid = b.id
        GROUP BY b.name, a.name, a.salary
)
WHERE rn <=3
Runtime: 1010 ms

This solution was interesting to know since I didn’t know that a ‘Windows’ operation can be performed with a ‘JOIN’ function.

Solution 3

SELECT D1.Name Department,E.Name Employee,E.salary 
FROM 
Employee E,Department D1,
(SELECT name, dense_rank() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) sal FROM Employee )e1
 
WHERE E.name = e1.name
 AND e1.sal < 4
 AND E.DepartmentId = D1.Id;
Runtime: 1024 ms

This was another interesting solution cause you can cross join multiple tables including tables that are formed using ‘SELECT’ statement.

Solution 4

SELECT temp.department AS Department,
       temp.employee AS Employee,
       temp.salary AS Salary
FROM (
    SELECT e.name AS employee, 
           e.salary AS salary, 
           d.name AS department,
           DENSE_RANK() OVER (PARTITION BY e.departmentid 
           ORDER BY e.salary DESC) AS rank
    FROM Employee e 
    JOIN Department d
    ON e.departmentid = d.id
) temp
WHERE temp.rank <= 3;
Runtime: 1071 ms

Disclaimer: I am currently learning Oracle SQL. Therefore, the above solutions are only going to work for Oracle SQL. The logic of the language not matter what brand you use will be the same. The only difference will arise from the differences in the syntaxes.

• Facebook
• Instagram
• LinkedIn
• GitHub