We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
Challenges table does not have all the hacker_id entries. Submissions table and Hacker table does. So if we join tables based on h.hacker_id=c.hacker_id then we are missing out on hacker_ids that are not present in Challenges table.
Trying joining Challenges table to Submissions table using challenge_id. It worked for me.
Also, once you have joined the tables it doesnt really matter if you use h.hacker_id or c.hacker_id or s.hacker_id in your ORDER BY clause. It will all work out the same. Atleast for hackerrank it does.
See below:
SELECT h.hacker_id, h.name
FROM SUBMISSIONS s
JOIN HACKERS h
ON s.hacker_id = h.hacker_id
JOIN CHALLENGES c
ON s.challenge_id = c.challenge_id
JOIN DIFFICULTY d
ON c.difficulty_level = d.difficulty_level
WHERE s.score = d.score
GROUP BY h.hacker_id, h.name
HAVING COUNT(h.hacker_id) > 1
ORDER BY COUNT(h.hacker_id) DESC, h.hacker_id
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Top Competitors
You are viewing a single comment's thread. Return to all comments →
Challenges table does not have all the hacker_id entries. Submissions table and Hacker table does. So if we join tables based on h.hacker_id=c.hacker_id then we are missing out on hacker_ids that are not present in Challenges table.
Trying joining Challenges table to Submissions table using challenge_id. It worked for me.
Also, once you have joined the tables it doesnt really matter if you use h.hacker_id or c.hacker_id or s.hacker_id in your ORDER BY clause. It will all work out the same. Atleast for hackerrank it does.
See below:
SELECT h.hacker_id, h.name FROM SUBMISSIONS s JOIN HACKERS h ON s.hacker_id = h.hacker_id JOIN CHALLENGES c ON s.challenge_id = c.challenge_id JOIN DIFFICULTY d ON c.difficulty_level = d.difficulty_level WHERE s.score = d.score GROUP BY h.hacker_id, h.name HAVING COUNT(h.hacker_id) > 1 ORDER BY COUNT(h.hacker_id) DESC, h.hacker_id