ugly solution that beats 97.53% -- w/o function count()


  • 0
    SELECT
      D.Name AS Department,
      E.Name AS Employee,
      E.Salary AS Salary
    FROM
      Employee E,
      Department D,
      (SELECT
        E2.DepartmentId,
        IFNULL(MAX(E3.Salary), -1) AS 3rdMaxSalary
      FROM (
        SELECT
          E0.DepartmentId,
          MAX(Salary) AS 2ndMaxSalary
        FROM (
          SELECT MAX(Salary) AS MaxSalary, DepartmentId FROM Employee GROUP BY DepartmentId) E0
            LEFT JOIN
          Employee E1
            ON E0.DepartmentId = E1.DepartmentId AND E1.Salary < E0.MaxSalary
          GROUP BY E0.DepartmentId) E2
        LEFT JOIN
          Employee E3
        ON E2.DepartmentId = E3.DepartmentId AND E3.Salary < E2.2ndMaxSalary
      GROUP BY E2.DepartmentId) T
    WHERE E.DepartmentId = T.DepartmentId AND T.DepartmentId = D.Id AND E.Salary >= T.3rdMaxSalary
    ORDER BY Department, Salary DESC
    

    With original data:
    0_1489657682573_upload-a24f5db9-d3a0-4f7f-b448-1839a306a63a
    this query can be shown as follows:

    1. get Max Salary of each department:
    SELECT MAX(Salary) AS MaxSalary, DepartmentId FROM Employee GROUP BY DepartmentId
    

    0_1489657559687_upload-7f0a2e46-e4c0-46c3-a930-efedde22f47b

    1. get 2nd-Max Salary of each department by querying MAX(salary < MaxSalary), and field 2ndMaxSalary can be NULL
        SELECT
          E0.DepartmentId,
          MAX(Salary) AS 2ndMaxSalary
        FROM (
          SELECT MAX(Salary) AS MaxSalary, DepartmentId FROM Employee GROUP BY DepartmentId) E0
            LEFT JOIN
          Employee E1
            ON E0.DepartmentId = E1.DepartmentId AND E1.Salary < E0.MaxSalary
          GROUP BY E0.DepartmentId
    

    0_1489657643362_upload-d19cdca4-df6b-4982-92eb-bac707202bd9

    1. get 3rd-Max Salary of each department, and filed 3rdMaxSalary should be updated to -1 if NULL:
    SELECT
        E2.DepartmentId,
        IFNULL(MAX(E3.Salary), -1) AS 3rdMaxSalary
      FROM (
        SELECT
          E0.DepartmentId,
          MAX(Salary) AS 2ndMaxSalary
        FROM (
          SELECT MAX(Salary) AS MaxSalary, DepartmentId FROM Employee GROUP BY DepartmentId) E0
            LEFT JOIN
          Employee E1
            ON E0.DepartmentId = E1.DepartmentId AND E1.Salary < E0.MaxSalary
          GROUP BY E0.DepartmentId) E2
        LEFT JOIN
          Employee E3
        ON E2.DepartmentId = E3.DepartmentId AND E3.Salary < E2.2ndMaxSalary
      GROUP BY E2.DepartmentId
    

    0_1489657806153_upload-90c025f1-d0b4-4b60-bf9c-5589f16fb2bc

    1. finally, we join the three tables and filter the answer.

Log in to reply
 

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