The Problem
Write a query to print the respective hacker_id
and name
of hackers who achieved full scores
for more than one challenge
. 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 ascending hacker_id
.
The Input
Sample:
The Output
Sample:
90411 Joe
Explanation
- Hacker 86870 got a score of 30 for challenge 71055 with a difficulty level of 2, so 86870 earned a full score for this challenge.
- Hacker 90411 got a score of 30 for challenge 71055 with a difficulty level of 2, so 90411 earned a full score for this challenge.
- Hacker 90411 got a score of 100 for challenge 66730 with a difficulty level of 6, so 90411 earned a full score for this challenge.
- Only hacker 90411 managed to earn a full score for more than one challenge, so we print their
hacker_id
andname
as 2 space-separated values.
The Solution
Observe the relationship across the table. From the input sample and the explanation, we can conclude that hacker_id
column in Challenges
table doesn't serve any purpose in this problem (purple annotations).
So, we join these 4 tables together like the following:
- Join
Submissions
table withHackers
table on bothhacker_id
- Join
Challenges
table withSubmissions
table on bothchallenge_id
- Join
Difficulty
table withChallenges
table on bothdifficulty_level
- Join
Difficulty
table withSubmissions
table on bothscore
to get whoever gets the top score of each challenge
The Code
SELECT
h.hacker_id, h.name
FROM
hackers h
JOIN submissions s ON h.hacker_id = s.hacker_id
JOIN challenges c ON s.challenge_id = c.challenge_id
JOIN difficulty d ON c.difficulty_level = d.difficulty_level AND s.score = d.score
GROUP BY
h.hacker_id,
h.name
HAVING
COUNT(c.challenge_id) > 1
ORDER BY
COUNT(c.challenge_id) DESC,
h.hacker_id
Top comments (0)