[Hackerrank] SQL - Occupations

문제

풀이

어떤 직업이 사람 수가 가장 많은지 알아야 가능한 풀이로 접근했다.
각 직업마다 가상 테이블을 만들었고, 교수가 직업인 사람이 7명으로 가장 많아 row_number를 키로 left join을 사용했다.
원래는 full outer join을 사용하려 했으나 MySQL에서는 지원하지 않는다고 한다.
그래도 쓰고 싶으면 left join 한 결과와 right join 한 결과를 union하라고 한다.

with doctor as
(select 
    name,
    row_number() over(order by name) as rn
from occupations
where occupation = 'Doctor'
order by 1),
professor as
(select
    name,
    row_number() over(order by name) as rn
from occupations
where occupation = 'Professor'
order by 1),
singer as
(select
    name,
    row_number() over(order by name) as rn
from occupations
where occupation = 'Singer'),
actor as
(select
    name,
    row_number() over(order by name) as rn
from occupations
where occupation = 'Actor')
select
    t1.name,
    t2.name,
    t3.name,
    t4.name
from professor t2
left join doctor t1
on t2.rn = t1.rn
left join singer t3
on t2.rn = t3.rn
left join actor t4
on t2.rn = t4.rn