SQL 문제 풀기_ HACKER RANK -Contest Leaderboard

[hdonghun]

HACKER RANK -Contest Leaderboard 출처 : https://www.hackerrank.com/challenges/contest-leaderboard/problem

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.

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. Sample Input:

Hackers Table:

Submissions Table:

Sample Output : 4071 Rose 191 74842 Lisa 174 84072 Bonnie 100 4806 Angela 89 26071 Frank 85 80305 Kimberly 67 49438 Patrick 43

[hdonghun]

MYSQL : SELECT h.hacker_id, h.name,SUM(score_max) total_score FROM ( SELECT hacker_id, challenge_id, MAX(score) score_max FROM Submissions GROUP BY hacker_id, challenge_id) t INNER JOIN Hackers h ON h.hacker_id = t.hacker_id GROUP BY h.hacker_id, h.name HAVING total_score != 0 ORder by total_score DESC, h.hacker_id

[hdonghun]

집계 함수 이용시 GROUP BY !!사용!!

[hdonghun]

MSSQL : select H.Hacker_id, h.Name, sum(s.Score) from Hackers h inner join (
select hacker_id, challenge_id, max(Score) as 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.score) > 0 order by sum(s.score) desc, h.hacker_id

[hdonghun]

MSSQL : 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 ) s inner 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