[HackerRank] Basic Join : Top Competitors

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respectivehacker_idandnameof hackers who achieved full scores formore than onechallenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascendinghacker_id.

Input Format

The following tables contain contest data:

• Hackers:Thehacker_idis the id of the hacker, andnameis the name of the hacker.

  

• Difficulty:Thedifficult_levelis the level of difficulty of the challenge, andscoreis the score of the challenge for the difficulty level.

  

• Challenges:Thechallenge_idis the id of the challenge, thehacker_idis the id of the hacker who created the challenge, anddifficulty_levelis the level of difficulty of the challenge.

  

• Submissions:Thesubmission_idis the id of the submission,hacker_idis the id of the hacker who made the submission,challenge_idis the id of the challenge that the submission belongs to, andscoreis the score of the submission.

  

---

Sample Input

HackersTable:

DifficultyTable:

ChallengesTable:

SubmissionsTable:

Sample Output

90411 Joe

Explanation

• Hacker86870 got a score of30for challenge71055with a difficulty level of2, so86870earned a full score for this challenge.
• Hacker90411 got a score of30for challenge71055with a difficulty level of2, so90411earned a full score for this challenge.
• Hacker90411 got a score of100for challenge66730with a difficulty level of6, so90411earned a full score for this challenge.
• Only hacker90411 managed to earn a full score for more than one challenge, so we print the their hacker_id and name as 2 space-separated values.

풀이

SELECT s.hacker_id, h.name 
    FROM Submissions s 
              JOIN Challenges c ON s.challenge_id = c.challenge_id 
              JOIN Difficulty d ON c.difficulty_level = d.difficulty_level  
              JOIN Hackers h ON s.hacker_id = h.hacker_id 
    WHERE s.score = d.score
    GROUP BY s.hacker_id, h.name 
    HAVING COUNT(s.hacker_id) > 1 
    ORDER BY COUNT(s.hacker_id) DESC, s.hacker_id ASC