[LeetCode-Medium] Second Highest Salary 두 번째로 높은 급여 구하기, EMPTY VALUE를 NULL로 출력하기

LeetCode - 176. Second Highest Salary, Reference

Difficulty 분류 : Medium

My Solution 1

/*
- to report the second highest salary from the Employee table. 
    -> 내림차순 DESC
- If there is no second highest salary, the query should report null.
*/

WITH CTE AS(
    -- 급여 내림차순 기준으로 순위를 생성
    SELECT 
        *,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
    FROM Employee
)

SELECT MAX(Salary) AS `SecondHighestSalary`
FROM CTE
WHERE rk = 2

Runtime: 293 ms, faster than 20.59% of MySQL online submissions for Second Highest Salary.

Lessons Learned

DENSE_RANK() vs RANK()

DENSE_RANK() OVER (ORDER BY ..)

EMPTY VALUE 를 NULL로 출력하기

IFNULL(val, null) will not return 'null' if the value is empty (IFNULL will assume empty is the value we want). To avoid this problem, use either MAX() or subquery (IFNULL((subquery), null).