[문제풀이] Contest Leaderboard

* 삽질 풀이과정을 담고 있습니다. 

해커랭크 Contest Leaderboard

Contest Leaderboard | HackerRank

You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too! The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of 0 from your result.

hackers

Submissions

1. hacker_id, name, total score은 DESC로 정렬
2. 동점자가 있는 경우, hacker_id는 ASC로 정렬  
3. total score가 0점인 경우는 제외하기

---

나의 시나리오 
1. submissions 테이블을 활용해 total score을 구한다. 
2. total score가 담긴 테이블과 hackers 테이블을 조인한다. 
3. 문제의 조건들을 걸어준다. 

그런데 처음 total score을 구하는 과정에서 많이 헤맸다. 왜냐하면 이 예시 테이블을 보면 hacker 4071의 경우 95점, 43점, 96점을 받았는데, 각 challenge_id에서 최대값 뽑기를 어떤 원리로 풀어야 할지 몰랐기 때문이다.

---

1)

SELECT hacker_id, challenge_id, score
FROM submissions 
GROUP BY hacker_id, challenge_id

그래서 맨 처음에 이렇게 접근했다. 결과는 에러코드. score를 group by 절에서 묶어주지 않았기 때문이다. 점수끼리 그룹바이를 해줄 수는 없을 것 같은데 1차 당황 

2)

SELECT *
FROM submissions s1
INNER JOIN submissions s2 ON s1.hacker_id = s2.hacker_id
WHERE s1.score < s2.score

이전에 DELETE 문제를 풀었던지라 submissoins 테이블끼리 조인한 다음, score의 조건이 S1< S2인 경우를 찾아 테이블을 만들고, score의 중복값을 제거하면 max score만 나올 것이라고 예상했다. 그리고 DELETE 해주면 되지 않을까? 물론 틀렸다.

3)

SELECT hacker_id, challenge_id, MAX(score) AS max_score
FROM submissions 
GROUP BY hacker_id, challenge_id  

--
예시 값
486 20594 45 
486 68420 29 

공책에 써가며 머리를 굴려봤지만 풀리지 않아서 미리 푼 스터디 멤버의 답을 보았다(!) 그런데 max(score) 이라고 아주 간단하게 푼 것이 아닌가? 난 max를 써주면 score에서 단 하나의 최대점수가 나온다고 생각했다. max 점수를 뽑아 sum을 할 것이니 이렇게 점수를 뽑으면 안될 것 같았다. 그런데 결과는 challenge_id 별로 max(score)가 나왔다. select 문에 순서에 따라 값이 달라질까 순서도 바꿔봤지만 답은 같았다. 아놔 나 뭐한걸까. 

4)

SELECT hacker_id, SUM(max_score) AS total_score
FROM (
    SELECT hacker_id, challenge_id, MAX(score) AS max_score
    FROM submissions 
    GROUP BY hacker_id, challenge_id    
    ) sub
GROUP BY hacker_id

----
예시 값 
486 74 

max_score 값을 구했으니 max_score을 더한 total_score 값을 구해준다. 

5)

SELECT a.hacker_id, a.name, b.total_score
FROM hackers AS a 
LEFT JOIN (
    SELECT hacker_id, SUM(max_score) AS total_score
    FROM (
        SELECT hacker_id, challenge_id, MAX(score) AS max_score
        FROM submissions 
        GROUP BY hacker_id, challenge_id    
        ) sub
    GROUP BY hacker_id
) b ON a.hacker_id = b.hacker_id
ORDER BY total_score DESC, hacker_id ASC

-- 
예시 값
97419 Brian 0 
99148 Kimberly 0 
11534 Kelly NULL 
21231 Martha NULL 
이런 친구들이 포함되게 된다! 

이제 total_score가 포한된 테이블을 hackers 테이블과 조인해준다. 물론 여기서도 헷갈렸다. 서브쿼리는 SELECT, FROM, WHERE 절에서 사용한다는 것은 알고있다. 그런데 내가 4번에서 만든 테이블 어느 부분에 hackers 테이블을 붙여야 할지 고민되었다. FROM절에 붙이려니 밑에 GROUP BY가 있는데 어떻게? 뭐? 이런식. 결국 구글링을 했고, hackers 테이블 from 절에 서브쿼리를 넣고, ) b ON a.hacker_id = b.hacker_id 를 통해 둘을 연결했다. 이런 모양이 낯설어서 그렇지 이제 모양새는 눈에 저장했다. 

6)

SELECT a.hacker_id, a.name, b.total_score
FROM hackers AS a 
LEFT JOIN (
    SELECT hacker_id, SUM(max_score) AS total_score
    FROM (
        SELECT hacker_id, challenge_id, MAX(score) AS max_score
        FROM submissions 
        GROUP BY hacker_id, challenge_id
        ) sub
    GROUP BY hacker_id
) b ON a.hacker_id = b.hacker_id
WHERE b.total_score != 0 AND b.total_score IS NOT NULL
ORDER BY total_score DESC, hacker_id ASC

이렇게 풀면 원하는 테이블은 다 만들어졌지만 0과 null 값까지 출력되어 조건에서 제거해줘야 한다. max_score 구하는 테이블에서 having 조건을 넣어 having max_score > 0으로 조건을 만들어보기도 했지만 NULL 값이 나왔다. 왜냐하면 나는 LEFT JOIN으로 걸었기 때문. INNER JOIN을 썼다면 having 절이 작동한다.

그리고 통과! 

 

참 우여곡절의 문제였다. 끝.