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.
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.
- 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
|[HackerRank] Basic Aggregation : The Blunder (0)||2021.02.09|
|[HackerRank] Basic Aggregation : Revising Aggregations (0)||2021.02.09|
|[HackerRank] Basic Join : Top Competitors (0)||2021.02.08|
|[HackerRank] Basic Join : The Report (2)||2021.02.08|
|[HackerRank] Basic Join : Average Population of Each Continent (0)||2021.02.08|
|[HackerRank] Basic Join : African Cities (0)||2021.02.08|