Contest Leaderboard

---

Link

1) The total score of a hacker is the sum of their maximum scores for all of the challenges.

2) Write a query to print the hacker_id, name, and total score of the hackers 3) ordered by the descending score.

4) If more than one hacker achieved the same total score, then sort the result by ascending hacker_id.

5) Exclude all hackers with a total score of 0 from your result.

풀이

---

• 각 해커가 제출한 challenge의 모든 점수를 합하여 total score 계산하기. 단, 같은 challenge_id를 가진 문제를 여러번 제출했을 경우, 최고점으로 뽑아서 계산

1. submissions 테이블에서 hacker_id, challenge_id 로 GROUP BY 후, MAX(score) 산출

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

486 20594 45 
486 68420 29 
597 20594 107 
775 68420 31 
964 28665 55 
1700 14825 66 
1700 38705 49 
1746 28665 32 
1755 28665 58 

2. 위의 테이블과 hackers 테이블을 JOIN

SELECT *
FROM hackers H
    INNER JOIN (SELECT hacker_id, challenge_id, MAX(score) MAX_SCORE
                FROM submissions
                GROUP BY hacker_id, challenge_id) S
    ON H.hacker_id = S.hacker_id

486 Rose 486 20594 45 
486 Rose 486 68420 29 
597 Angela 597 20594 107 
775 Frank 775 68420 31 
964 Patrick 964 28665 55 
1700 Lisa 1700 14825 66 
1700 Lisa 1700 38705 49 
1746 Kimberly 1746 28665 32 
1755 Bonnie 1755 28665 58 

3. 위 테이블에서 hacked_id, hacker_name 으로 GROUP BY 후, SUM 으로 점수 총합 산출

SELECT H.hacker_id, H.name, SUM(S.MAX_SCORE)

FROM hackers H
    INNER JOIN (SELECT hacker_id, challenge_id, MAX(score) MAX_SCORE
                FROM submissions
                GROUP BY hacker_id, challenge_id) S
    ON H.hacker_id = S.hacker_id

GROUP BY H.hacker_id, H.name

486 Rose 74 
597 Angela 107 
775 Frank 31 
964 Patrick 55 
1700 Lisa 115 
1746 Kimberly 32 
1755 Bonnie 58 
1869 Michael 120 
2380 Todd 175 

4. 점수 총합이 0 인 hacker 제외

SELECT H.hacker_id, H.name, SUM(S.MAX_SCORE)

FROM hackers H
    INNER JOIN (SELECT hacker_id, challenge_id, MAX(score) MAX_SCORE
                FROM submissions
                GROUP BY hacker_id, challenge_id) S
    ON H.hacker_id = S.hacker_id

GROUP BY H.hacker_id, H.name
HAVING SUM(S.MAX_SCORE) != 0

5. ORDER BY 조건 추가

SELECT H.hacker_id, H.name, SUM(S.MAX_SCORE)

FROM hackers H
    INNER JOIN (SELECT hacker_id, challenge_id, MAX(score) MAX_SCORE
                FROM submissions
                GROUP BY hacker_id, challenge_id) S
    ON H.hacker_id = S.hacker_id

GROUP BY H.hacker_id, H.name
HAVING SUM(S.MAX_SCORE) != 0
ORDER BY SUM(S.MAX_SCORE) DESC, H.hacker_id

My Answer

---

SELECT H.hacker_id, H.name, SUM(S.MAX_SCORE)

FROM hackers H
    INNER JOIN (SELECT hacker_id, challenge_id, MAX(score) MAX_SCORE
                FROM submissions
                GROUP BY hacker_id, challenge_id) S
    ON H.hacker_id = S.hacker_id

GROUP BY H.hacker_id, H.name
HAVING SUM(S.MAX_SCORE) != 0
ORDER BY SUM(S.MAX_SCORE) DESC, H.hacker_id

• subquery
  https://www.youtube.com/watch?v=Hj5edTNJe_Y