MySQL WITH문 활용[해커랭크]

https://www.hackerrank.com/challenges/challenges/problem?h_r=internal-search 

Challenges | HackerRank

이번 문제는 손이 많이 가는 서브쿼리 퀴즈인데요. 우선 예제를 살펴보겠습니다.

네 문제는 Hackers와 Challenges라는 두 테이블을 가지고 문제를 푸는 것입니다. 이 문제가 어려운 이유는 조건인데요. 조건을 간단하게 요약하면 다음과 같습니다. 

###조건

조건1. hacker_id 별 name과 challenges의 개수를 구해라
조건2. hacker_id 별 최대 challenges를 기록한 건 중복 표출이 가능하다.
조건3. 최대 challenges 외의 중복 challenges를 기록한 건 표출할 수 없다.

이처럼 상당히 어려운 조건들이 있는데요. 우선 조건 2를 구하기 위해서는 서브쿼리를 활용해 max 숫차를 먼저 구해야 겠네요. 우선 max값을 먼저 구해보겠습니다. 

SELECT ch.hacker_id, ha.name, COUNT(ch.challenge_id) AS ct
FROM Challenges AS ch
JOIN Hackers AS ha
ON ch.hacker_id = ha.hacker_id
GROUP BY ch.hacker_id, ha.name

/*MAX 찾기*/
HAVING ct = (select Max(cc)
             from (select hacker_id, count(*)as cc 
                   from Challenges group by hacker_id)
                   as sub)
_________________________________________________________________
output:
5120 Julia 50
20023 Brian 50
69471 Michelle 50
96716 Emily 50
96009 Russell 50
33625 Jason 50
18425 Anna 50
52462 Nicholas 50
64036 Craig 50
41805 Benjamin 50
77173 Mildred 50
94278 Dennis 50

having절 안에 서브쿼리를 중첩해서 최대 첼린지 카운트(ct)를 구했습니다. 그다음에는 가장 어려운 3번 조건을 해결해야 하는데요. 3번 조건은 or 안에 서브쿼리를 넣어야 하는데요. 여기서 중요한 것은 최대개수 외에는 중복이 없어야 한다는 겁니다. 그렇기 때문에 첼린지 개수를 기준으로 이 챌린지 개수에 해당하는 row 개수가 1인 값들만 뽑으면 됩니다. 이제 or절을 포함해 정답을 표출해 보겠습니다. 

 

SELECT ch.hacker_id, ha.name, COUNT(ch.challenge_id) AS ct
FROM Challenges AS ch
JOIN Hackers AS ha
ON ch.hacker_id = ha.hacker_id
GROUP BY ch.hacker_id, ha.name

/*MAX 찾기*/
HAVING ct = (select Max(cc)
             from (select hacker_id, count(*)as cc 
                   from Challenges group by hacker_id)
                   as sub)
/*최대가 아니면서 퀴즈를 낸 횟수가 중복되지 않은 사람들 찾기*/                   
OR ct IN (select cc
          from(select hacker_id, count(*)as cc 
               from Challenges group by hacker_id) suba
          group by cc
          having count(*) = 1
          order by cc desc, hacker_id)
          
ORDER BY ct DESC, ch.hacker_id
______________________________________________________________________
OUTPUT: 
5120 Julia 50
18425 Anna 50
20023 Brian 50
33625 Jason 50
41805 Benjamin 50
52462 Nicholas 50
64036 Craig 50
69471 Michelle 50
77173 Mildred 50
94278 Dennis 50
96009 Russell 50
96716 Emily 50
72866 Eugene 42
37068 Patrick 41
12766 Jacqueline 40
86280 Beverly 37
19835 Joyce 36
38316 Walter 35
29483 Jeffrey 34
23428 Arthur 33
95437 George 32
46963 Barbara 31
87524 Norma 30
84085 Johnny 29
39582 Maria 28
65843 Thomas 27
5443 Paul 26
52965 Bobby 25
77105 Diana 24
33787 Susan 23
45855 Clarence 22
33177 Jane 21
7302 Victor 20
54461 Janet 19
42277 Sara 18
99388 Mary 16
31426 Carlos 15
95010 Victor 14
27071 Gerald 10
90267 Edward 9
72609 Bobby 8

네 이렇게 정답을 표출했는데요. 하지만 코드가 상당히 복잡해지고 말았습니다. 이렇게 복잡한 쿼리를 좀 더 쉽게 만들어줄 수 있는 방법이 있는데 바로 with문을 활용하는 것입니다.  with는 가상의 테이블을 만드는 구문이고 with안에 만든 테이블은 계속해서 사용할 수 있다는 장점이 있습니다. 

WITH easy_count AS(SELECT ch.hacker_id, ha.name, COUNT(ch.challenge_id) AS ct
                   FROM Challenges AS ch
                   JOIN Hackers AS ha
                   ON ch.hacker_id = ha.hacker_id
                   GROUP BY ch.hacker_id, ha.name)

SELECT easy_count.hacker_id,
       easy_count.name,
       easy_count.ct
FROM easy_count
WHERE ct = (select max(easy_count.ct) from easy_count)
OR ct IN (select easy_count.ct from easy_count group by easy_count.ct having count(*) = 1)
ORDER BY easy_count.ct DESC, easy_count.hacker_id
____________________________________________________________________________________________
output:
5120 Julia 50
18425 Anna 50
20023 Brian 50
33625 Jason 50
41805 Benjamin 50
52462 Nicholas 50
64036 Craig 50
69471 Michelle 50
77173 Mildred 50
94278 Dennis 50
96009 Russell 50
96716 Emily 50
72866 Eugene 42
37068 Patrick 41
12766 Jacqueline 40
86280 Beverly 37
19835 Joyce 36
38316 Walter 35
29483 Jeffrey 34
23428 Arthur 33
95437 George 32
46963 Barbara 31
87524 Norma 30
84085 Johnny 29
39582 Maria 28
65843 Thomas 27
5443 Paul 26
52965 Bobby 25
77105 Diana 24
33787 Susan 23
45855 Clarence 22
33177 Jane 21
7302 Victor 20
54461 Janet 19
42277 Sara 18
99388 Mary 16
31426 Carlos 15
95010 Victor 14
27071 Gerald 10
90267 Edward 9
72609 Bobby 8

네 이렇게 1차적으로 조인한 값을 가상의 테이블로 만들어 최종 값을 내는 쿼리를 간단하게 만들 수 있습니다. 서브쿼리가 복잡해질 때 with문으로 좀 더 간단하게 만드는 연습을 자주 해봐야 겠습니다.