[HackerRank SQL] Occupations

문제 link
https://www.hackerrank.com/challenges/occupations/problem

Code link
https://github.com/JamesJung01/Hackerrank_sql/blob/main/02_Medium/Occupations.sql

SET @R1=0, @R2=0, @R3=0, @R4=0;
SELECT MIN(DOCTOR), MIN(PROFESSOR), MIN(SINGER), MIN(ACTOR)
FROM (
    SELECT 
    CASE 
        WHEN OCCUPATION = 'DOCTOR' THEN (@R1 := @R1 + 1)
        WHEN OCCUPATION = 'PROFESSOR' THEN (@R2 := @R2 + 1)
        WHEN OCCUPATION = 'SINGER' THEN (@R3 := @R3 + 1)
        WHEN OCCUPATION = 'ACTOR' THEN (@R4 := @R4 + 1)
    END AS ROWNUMBER,
    CASE WHEN OCCUPATION = 'DOCTOR' THEN NAME END AS DOCTOR,
    CASE WHEN OCCUPATION = 'PROFESSOR' THEN NAME END AS PROFESSOR,
    CASE WHEN OCCUPATION = 'SINGER' THEN NAME END AS SINGER,
    CASE WHEN OCCUPATION = 'ACTOR' THEN NAME END AS ACTOR
    FROM OCCUPATIONS
    ORDER BY NAME
) TEMP
GROUP BY ROWNUMBER;