[Mysql] Subquery 심화 실전

HackerRank

Challenges

문제 해석부터가 난관이었던 문제

wow.. 문제 해석 및 이해하는데만 10분 걸렸다!
문제를 요약해보자면

1) 주어진 테이블은 Hackers(hacker_id, name)와 Challenges(challenge_id, hacker_id)
2) select해야할 것은: hacker_id, name,
count(challenge_id) (--> group by 필요함)
3) 먼저 count(challenge_id)로 내림차순 정렬할 것
4) 만일 두 명 이상이 같은 count(challenge_id)를 가지고 있다면 hacker_id 순으로 정렬할 것
5) 만일 두 명 이상이 같은 count(challenge_id)를 가지고 있는데 그 수가 최댓값이 아니라면, 그 학생들은 모두 결과에서 제외시킬 것

난이도가 easy가 아니라 medium만 되어도 이정도라니!
정말 너무한 것 아닌가 싶었지만 우선 시도했다.

나의 풀이

select sub.hacker_id, sub.name, sub.counting
from (select h.hacker_id as hacker_id, h.name as name, count(c.challenge_id) as counting
      from Hackers as h
      join Challenges as c
      on h.hacker_id=c.hacker_id
      group by h.hacker_id, h.name
      order by counting desc) as sub
where sub.counting = max(sub.counting) or sub.counting < 2
group by sub.hacker_id, sub.name
order by sub.counting

너무 말도 안되는 식이 많아서 창피하다.

해설 강의 풀이 1 - subquery만 이용

select hackers.hacker_id, hackers.name, count(*) as challenges_created
from Challenges 
inner join Hackers 
on Challenges.hacker_id = Hackers.hacker_id
group by hackers.hacker_id, hackers.name
having challenges_created = (select max(sub.challenges_created)
                             from (select hacker_id, count(*) as challenges_created
                                   from Challenges
                                   group by hacker_id) as sub)
or challenges_created in (select sub.challenges_created
                          from (select hacker_id, count(*) as challenges_created
                                from Challenges
                                group by hacker_id) as sub
                          group by sub.challenges_created
                          having count(*) = 1)
order by challenges_created desc, hacker_id

부족했던 점

• having 사용: group by에 대한 조건 쿼리는 having절로 하면 되는데 아직까지는 상대적으로 더 익숙한 where만 떠오른다.
• having절 subquery 작성법: 첫 having절은 challeneges_created가 최댓값을 가진다면 그 학생들이 몇명이건간에 result set에 남길 수 있게 하는 쿼리문이다. 그리고 or로 연결된 두번째 having절은 challenges_created가 최댓값이 아닌 학생들 중 오로지 challenges_created가 겹치지 않는, 즉 challenges_created기준으로 학생들을 세었을 때 그 count가 1인 학생들만 보여주겠다는 쿼리문이다.
• order by절 중복 조건 사용: 문제 조건 3), 4)번을 한번에 적용시킬 수 있게 작성하는 법을 몰랐다. challneges_created로 내림차순 정렬 후 그래도 같은 값이 있다면 그 때는 hacker_id로 정렬하는 식.

해설 강의 풀이 2 - with구문 활용

위에서 풀어봤던 방식은 괜찮긴했으나 똑같은 식이 반복되어 사용된다는 점에서 다소 비효율적이었다. 이런 비효율성을 해결하기 위한 장치로 with문이 있다.

with counter as (select hackers.hacker_id, hackers.name, count(*) as challneges_created
                 from Challenges
                 inner join Hackers 
                 on Challenges.hacker_id=Hackers.hacker_id
                 group by hackers.hacker_id, hackers.name)
                 
select counter.hacker_id, counter.name, counter.challenges_created
from counter
where counter.challenges_created = (select MAX(counter.challenges_created) from counter)
or challenges_created in (select counter.challenges_created, count(*)
                          from counter
                          group by counter.challenges_created
                          having count(*)=1)
order by counter.challenges_created desc, counter.hacker_id
	

계속해서 등장하는 부분을 with A as B 구문으로 묶어둔 뒤 식을 쓰면 훨씬 더 깔끔하게 사용할 수 있다.