[Stratascratch - HARD] Unique Highest Salary (SQL 명령어 실행 순서, CASE문으로 변수 만들기, 최댓값 구하기)

Salesforce Interview Question

Problem

Difficulty level : Hard, Interview Question Date: May 2019

Find the highest salary among salaries that appears only once.

Table : employee

My Solution

My Logic

1. salary 가 unique value인지 확인하기 위해 해당 숫자가 한번 등장했는 지 판단하는 변수 once를 포함한 CTE를 만든다.
   • CASE WHEN COUNT(*) OVER (PARTITION BY salary) = 1 THEN TRUE END AS once
   • once는 TRUE 혹은 NULL 값을 가진다.
2. CTE에서 once=TRUE 인 즉, 한번만 등장한 salary 중 최댓값을 구한다.
   • 최댓값을 구하기 위해 once 로 GROUP BY + MAX

/*
Find the highest salary among salaries that appears only once.
*/

-- COUNT THE NUMBER OF salary TO FIND THE UNIQUE SALARY 
WITH CTE AS (
    SELECT 
        salary,

        -- BOOL VARIABLE : to check that the salary value appears only once.
        CASE 
            WHEN COUNT(*) OVER (PARTITION BY salary) = 1 
            THEN TRUE 
            END AS `once`

    FROM employee
)

-- OUTPUT highest salaray
SELECT MAX(salary) AS `highest_salary`
FROM CTE
-- Among salaries that appears only once.
WHERE once = TRUE
GROUP BY once

Lessons learned

SQL Order of Operations 실행 순서

최댓값을 구하기 위해 once 로 GROUP BY + MAX 를 사용했다. 쿼리 실행 과정은 다음과 같다.

1. FROM CTE : once변수에 해당 급여가 1번만 등장했는 지 여부를 판단(TRUE or NULL)한 결과를 저장했다.
2. WHERE : once=TRUE로 필터링한다.
3. GROUP BY : 집계함수 MAX() 를 적용할 레코드들은 모두 once=TRUE 이기 때문에 GROUP BY once 를 사용하면 문제에서 원하는 결과를 출력할 수 있다.

-- OUTPUT highest salaray
SELECT MAX(salary) as `highest_salary`
FROM CTE
-- Among salaries that appears only once.
WHERE once = TRUE
GROUP BY once

---

Source : SQL Order of Operations