LeetCode 코딩 테스트 - Second Highest Salary(LV.Medium)

 

 

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.

 

Write a solution to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).

The result format is in the following example.

 

Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

 

# 쿼리를 작성하는 목표, 확인할 지표 : 두번째로 높은 연봉 출력 / salary
# 쿼리 계산 방법 : 1. 조건문으로 max보다 작은 경우만 -> 2. 조건에 맞는 값 중에 max값(=2번째로 큰 값) -> 3. coalesce사용 없으면 null
# 데이터의 기간 : x
# 사용할 테이블 : employee
# JOIN KEY : x
# 데이터 특징 : x
SELECT
    # 2 & 3
    DISTINCT COALESCE(MAX(salary), NULL) AS secondhighestsalary
FROM employee
# 1
WHERE
    salary < (SELECT MAX(salary) FROM employee)