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.
WITH challenge_numbers AS (SELECT hacker_id, count() AS challenge_number FROM Challenges GROUP BY hacker_id),
max_challenge_number AS (SELECT max(challenge_number) FROM challenge_numbers),
single_occurences AS (SELECT challenge_number FROM challenge_numbers GROUP BY challenge_number HAVING count() = 1)
SELECT h.hacker_id, h.name, c.challenge_number
FROM Hackers h
INNER JOIN challenge_numbers c ON h.hacker_id = c.hacker_id
WHERE c.challenge_number in (SELECT * FROM max_challenge_number) OR
c.challenge_number in (SELECT * FROM single_occurences)
ORDER BY c.challenge_number 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
Challenges
You are viewing a single comment's thread. Return to all comments →
WITH challenge_numbers AS (SELECT hacker_id, count() AS challenge_number FROM Challenges GROUP BY hacker_id), max_challenge_number AS (SELECT max(challenge_number) FROM challenge_numbers), single_occurences AS (SELECT challenge_number FROM challenge_numbers GROUP BY challenge_number HAVING count() = 1) SELECT h.hacker_id, h.name, c.challenge_number FROM Hackers h INNER JOIN challenge_numbers c ON h.hacker_id = c.hacker_id WHERE c.challenge_number in (SELECT * FROM max_challenge_number) OR c.challenge_number in (SELECT * FROM single_occurences) ORDER BY c.challenge_number DESC, h.hacker_id;