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.
-- the first Cte Returns all hackers including those who made duplicated challenges and less than the maximum number of challenges
WITH all_hackers AS(SELECT c.hacker_id,h.name,COUNT(*) AS total_challenges
FROM Challenges c
JOIN Hackers h
ON c.hacker_id=h.hacker_id
GROUP BY c.hacker_id,h.name
ORDER BY total_challenges DESC),
-- this Cte returns duplicated total challenges that less than the maximum number of challenges
duplicated_challenges AS(SELECT total_challenges
FROM all_hackers
WHERE total_challenges < (SELECT MAX(total_challenges) FROM all_hackers)
GROUP BY total_challenges
HAVING COUNT(*) >1
)
--- the final query returns hackers without those who made the same number of challenges but less than Max
SELECT *
FROM all_hackers
WHERE total_challenges NOT IN(SELECT total_challenges FROM duplicated_challenges)
ORDER BY total_challenges DESC,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 →
MySql :
-- the first Cte Returns all hackers including those who made duplicated challenges and less than the maximum number of challenges
WITH all_hackers AS(SELECT c.hacker_id,h.name,COUNT(*) AS total_challenges FROM Challenges c JOIN Hackers h ON c.hacker_id=h.hacker_id GROUP BY c.hacker_id,h.name ORDER BY total_challenges DESC),
-- this Cte returns duplicated total challenges that less than the maximum number of challenges
duplicated_challenges AS(SELECT total_challenges FROM all_hackers WHERE total_challenges < (SELECT MAX(total_challenges) FROM all_hackers) GROUP BY total_challenges HAVING COUNT(*) >1 )
--- the final query returns hackers without those who made the same number of challenges but less than Max SELECT * FROM all_hackers WHERE total_challenges NOT IN(SELECT total_challenges FROM duplicated_challenges) ORDER BY total_challenges DESC,hacker_id