[MySQL/HackerRank] Occupations

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

Note: Print NULL when there are no more names corresponding to an occupation.

Input Format

The OCCUPATIONS table is described as follows:

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

Sample Output

Jenny Ashley Meera Jane
Samantha Christeen Priya Julia
NULL Ketty NULL Maria

 

 

Explanation

The first column is an alphabetically ordered list of Doctor names.
The second column is an alphabetically ordered list of Professor names.
The third column is an alphabetically ordered list of Singer names.
The fourth column is an alphabetically ordered list of Actor names.
The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values.

 

WITH SortedOccupations AS (
    SELECT
        Name,
        Occupation,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) as RowNum
    FROM Occupations
) -- 각 직업별로 이름을 알파벳 순서로 정렬하고, 각 행에 순번을 매기는 CTE

SELECT
    MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) as Doctor,
    MAX(CASE WHEN Occupation = 'Professor' THEN Name END) as Professor,
    MAX(CASE WHEN Occupation = 'Singer' THEN Name END) as Singer,
    MAX(CASE WHEN Occupation = 'Actor' THEN Name END) as Actor
FROM
    SortedOccupations
GROUP BY RowNum
ORDER BY RowNum;

MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) as Doctor
--  Occupation이 'Doctor'인 경우에만 Name을 반환하고, 그렇지 않으면 NULL을 반환
-- MAX 함수는 GROUP BY된 행에서 유일한 값을 선택하는 역할

 

WITH SortedOccupations AS (
    SELECT
        Name,
        Occupation,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) as RowNum
    FROM Occupations
)
SELECT
    COALESCE(MAX(CASE WHEN Occupation = 'Doctor' THEN Name END), 'NULL') as Doctor,
    COALESCE(MAX(CASE WHEN Occupation = 'Professor' THEN Name END), 'NULL') as Professor,
    COALESCE(MAX(CASE WHEN Occupation = 'Singer' THEN Name END), 'NULL') as Singer,
    COALESCE(MAX(CASE WHEN Occupation = 'Actor' THEN Name END), 'NULL') as Actor
FROM
    SortedOccupations
GROUP BY RowNum
ORDER BY RowNum;

COALESCE(MAX(CASE WHEN Occupation = 'Doctor' THEN Name END), 'NULL') as Doctor

-- CASE WHEN Occupation = 'Doctor' THEN Name END는 Occupation이 'Doctor'인 경우에만 Name을 반환하고, 그렇지 않으면 NULL을 반환
-- MAX 함수는 GROUP BY된 각 RowNum에서 해당 직업의 이름을 선택
--- 만약 해당 RowNum에 해당 직업의 이름이 없다면 MAX는 NULL 값을 반환
-- 마지막으로, COALESCE 함수는 NULL 값을 'NULL' 문자열로 대체

 

COALESCE

• 여러 인자들 중에 NULL이 아닌 첫 번째값을 반환

SELECT COALESCE(NULL, 1, 2, 3);
-- 1

SELECT COALESCE(1, 2, NULL, 'MySQL');
-- MySQL

 

 

HackerRank MySQL Medium

참고 사이트 : https://dev.mysql.com/doc/mysql-partitioning-excerpt/8.0/en/partitioning-list.html