[HackerRank] Conest Leaderboard

https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true

Contest Leaderboard | HackerRank

각 hacker의 total_score는 challange 점수 중 최고점들을 합한 것

hacker_id, name, total_score를 출력

total_score DESC, hacker_id 로 정렬

total_score가 0인 경우는 제외

 

1. 각 hacker의 challage별 최고점을 구하는 쿼리 작성

SELECT challenge_id, hacker_id, MAX(score) AS score
FROM submissions
GROUP BY  challenge_id, hacker_id) s ON h.hacker_id = s.hacker_id

 

2. 이름을 출력해주기 위해 1번 쿼리에 hacker 테이블을 조인

SELECT s.hacker_id,
    h.name,
    SUM(score) AS total_score
FROM (SELECT challenge_id, hacker_id, MAX(score) AS score
	FROM submissions
	GROUP BY  challenge_id, hacker_id) s ON h.hacker_id = s.hacker_id) s
    INNER JOIN hackers h ON s.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
HAVING total_score != 0
ORDER BY total_score DESC, hacker_id