문제
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.
Input Format
The following tables contain contest data:
- Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
- Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission.
1. 해커 아이디, 이름, 전체점수를 나타내는 쿼리 입력
2. 전체점수(total score) 은 해커가 푼 챌린지 중 가장 높은 점수끼리 더한 값
*한 해커가 동일한 챌린지를 여러 번 풀기도 함
3. 전체 점수가 0 인 경우는 제외하기
4. 결과값은 전체 점수가 높은 순(내림차순) 정렬, 점수같으면 해커아이디 오름차순 정렬
풀이
서브쿼리로 문제풀이
SELECT h.hacker_id, h.name, sum(s.score)
FROM (SELECT hacker_id , challenge_id , max(score) AS score
FROM submissions
GROUP BY hacker_id, challenge_id) AS s
JOIN hackers h ON h.hacker_id = s.hacker_id
GROUP BY h.hacker_id, h.name
HAVING sum(s.score) <> 0
ORDER BY sum(s.score) DESC , h.hacker_id
'SQL > MySQL 문제풀이' 카테고리의 다른 글
[HackerRank] Challenges Solution(풀이방법) (0) | 2022.06.01 |
---|---|
[HackerRank] Binary Tree Nodes Solution(풀이) (0) | 2022.06.01 |
[HackerRank] Top Competitors Solution(풀이) (0) | 2022.05.29 |
[HackerRank] Placements Solution(풀이 2가지) (0) | 2022.05.29 |
[HackerRank] The Report Solution(풀이) (0) | 2022.05.29 |