[Hackerrank] SQL - The PADS

문제

풀이

처음에는 하나의 쿼리문으로 답을 내야하는 줄 알고 union all을 사용하여 다음과 같이 작성하였는데, 사람 이름이 알파벳순으로 정렬되지 않았다.

왜 정렬이 되지 않는지 아직도 이유를 잘 모르겠다...

with t1 as
(select 
    name,
    case
        when occupation = 'Doctor' then concat(name, '(D)')
        when occupation = 'Actor' then concat(name, '(A)')
        when occupation = 'Singer' then concat(name, '(S)')
        when occupation = 'Professor' then concat(name, '(P)')
    end as occ
from occupations
order by name), t2 as
(select
    occupation,
    count(*) as cnt
from occupations
group by occupation
order by cnt), t3 as
(select
    concat('There are a total of ', cnt, ' ', lower(occupation),'s.') as info
from t2
order by cnt, occupation)
select
    t1.occ
from t1
union all
select
    t3.info
from t3

그래서 문제를 다시 읽어보니 2개의 쿼리문을 작성하는 것이었다.
따라서 다음과 같이 작성하였다.

select 
    case
        when occupation = 'Doctor' then concat(name, '(D)')
        when occupation = 'Actor' then concat(name, '(A)')
        when occupation = 'Singer' then concat(name, '(S)')
        when occupation = 'Professor' then concat(name, '(P)')
    end as occ
from occupations
order by name;

with t1 as
(select
    occupation,
    count(*) as cnt
from occupations
group by occupation
order by cnt)
select
    concat('There are a total of ', cnt, ' ', lower(occupation),'s.') as info
from t1
order by cnt, occupation;