Prepare > SQL > Basic Join > 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  from your result.

 

=> hacker_id, name, 문제의 최고점의 합을 문제의 최고점 합, hacker_id 기준으로 쿼리하라. 합이 0 점인 사람들은 제외.

 

 

 

 

코드

---

SELECT 
    hacker_id,
    name,
    SUM(k1)

FROM

(SELECT S.hacker_id, H.name, S.challenge_id, MAX(score) as k1
FROM SUBMISSIONS AS S
    INNER JOIN HACKERS AS H ON S.hacker_id = H.hacker_id
GROUP BY S.hacker_id, H.name, S.challenge_id) AS K

GROUP BY hacker_id, name
HAVING SUM(k1) <> 0

ORDER BY SUM(k1) DESC, hacker_id;

두 번째 시도 코드

SELECT K.hacker_id, name, SUM(score)
FROM (
    SELECT hacker_id, challenge_id, MAX(score) AS score
    FROM SUBMISSIONS
    WHERE score <> 0
    GROUP BY hacker_id, challenge_id) AS K
LEFT JOIN HACKERS AS H ON K.hacker_id = H.hacker_id
GROUP BY K.hacker_id, name
ORDER BY SUM(score) DESC, hacker_id;

 

 

 

 

노트

---

필요한 데이터 모음 만들기

(SELECT S.hacker_id, H.name, S.challenge_id, MAX(score) as k1
FROM SUBMISSIONS AS S
    INNER JOIN HACKERS AS H ON S.hacker_id = H.hacker_id
GROUP BY S.hacker_id, H.name, S.challenge_id) AS K

• 해커별(이름별), 문제별 최고점 을 조회

 

 

해커별(이름별), 문제별 최고점의 합 조회

SELECT 
    hacker_id,
    name,
    SUM(k1)

FROM
(SELECT S.hacker_id, H.name, S.challenge_id, MAX(score) as k1
FROM SUBMISSIONS AS S
    INNER JOIN HACKERS AS H ON S.hacker_id = H.hacker_id
GROUP BY S.hacker_id, H.name, S.challenge_id) AS K

GROUP BY hacker_id, name

 

 

총합 0점 제외, 조회컬럼 정렬

SELECT 
    hacker_id,
    name,
    SUM(k1)

FROM
(SELECT S.hacker_id, H.name, S.challenge_id, MAX(score) as k1
FROM SUBMISSIONS AS S
    INNER JOIN HACKERS AS H ON S.hacker_id = H.hacker_id
GROUP BY S.hacker_id, H.name, S.challenge_id) AS K

GROUP BY hacker_id, name
HAVING SUM(k1) <> 0

ORDER BY SUM(k1) DESC, hacker_id;