HackerRank lv. Medium : Occupations

문제

hackerrank sql occupations

Pivot : https://en.wikipedia.org/wiki/Pivot_table

 

 

풀이

SELECT 
    MAX(CASE WHEN occupation="Doctor" THEN name ELSE null END) AS doctor,
    MAX(CASE WHEN occupation="Professor" THEN name ELSE null END) AS professor,
    MAX(CASE WHEN occupation="Singer" THEN name ELSE null END) AS singer,
    MAX(CASE WHEN occupation="Actor" THEN name ELSE null END) AS actor
FROM (
    SELECT name, occupation, ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) row_num
    FROM occupations
    ) numbered_table
GROUP BY row_num;

 

 

위의 SQL 쿼리는 주어진 직업(OCCUPATIONS) 테이블에서 각 직업별로 최대 값(MAX)을 구하는 쿼리입니다. 각 직업별로 이름을 가져오고, 이름을 특정 직업에 따라서 CASE 문을 사용하여 분류하고 있습니다. 이를 통해 특정 직업의 이름을 가져올 수 있습니다.

그런 다음, ROW_NUMBER() 함수를 사용하여 각 직업별로 번호를 부여합니다. 이는 같은 직업끼리는 같은 번호를 가지고 이름을 정렬합니다. PARTITION BY 절을 사용하여 직업별로 파티션을 나누고, ORDER BY 절을 사용하여 이름을 기준으로 정렬합니다.

그 후, 각 직업별로 최대 값을 구하기 위해 MAX() 함수를 사용하고, GROUP BY 절을 통해 각 직업별로 그룹화합니다. 이렇게 함으로써 각 직업별로 최대 값(가장 알파벳순으로 먼저 오는 이름)을 얻을 수 있습니다.

 

 

 

 

 

 

https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=true

Occupations | HackerRank