Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! 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.
My solution that doesn't work:
SELECT h.hacker_id, h.name
FROM Hackers h
JOIN Challenges c
ON h.hacker_id = c.hacker_id
JOIN Difficulty d
ON c.difficulty_level = d.difficulty_level
JOIN Submissions s
ON s.score = d.score
-- no where clause
GROUP BY h.hacker_id, h.name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC, h.hacker_id;
Solution that works:
select h.hacker_id, h.name
from Submissions as s
join Hackers as h
on s.hacker_id = h.hacker_id
join Challenges as c
on s.challenge_id = c.challenge_id
join Difficulty as d
on c.Difficulty_level = d.Difficulty_level
-- the only real difference:
where s.score = d.score
group by h.hacker_id, h.name
having count(*) > 1
order by count(*) desc, h.hacker_id;
Why does having s.score = d.score in the WHERE clause make the query work, but having it in an ON clause as part of an INNER JOIN make it not work (on HackerRank.com where the query comes from)? I thought for INNER JOINs it didn't matter, because the optimizer rearranges them at will?
How do I know when to use something like s.score = d.score (or whatever the columns are) in a WHERE clause and not in an ON clause as part of an INNER JOIN?
