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.
I pasted your MSSQL solution and got an error. I have been unsuccessful at using CTEs on HackerRank. I am not sure if this is HackerRanks issue or mine.
My solution could use some CTEs to clean it up.
SET @max = (SELECT count(*)
FROM Hackers h, Challenges c
WHERE h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
ORDER BY count(*) DESC
LIMIT 1);
SELECT h.hacker_id, h.name, count(*) as count
FROM Hackers h, Challenges c
WHERE h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
-- Having the max count
HAVING count(*) = @max
UNION ALL
SELECT h.hacker_id, h.name, count(*) as count
FROM Hackers h, Challenges c
WHERE h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
HAVING count(*) IN
-- a list of challenge counts that = 1 and not max
(SELECT a.challengeCounts
FROM(SELECT h.hacker_id, h.name, count(*) as challengeCounts
FROM Hackers h, Challenges c
WHERE h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name) a
GROUP BY a.challengeCounts
-- Having count less than the max count
HAVING count() < @max
AND count()=1)
ORDER BY count 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 →
I pasted your MSSQL solution and got an error. I have been unsuccessful at using CTEs on HackerRank. I am not sure if this is HackerRanks issue or mine.
My solution could use some CTEs to clean it up.
SET @max = (SELECT count(*) FROM Hackers h, Challenges c WHERE h.hacker_id = c.hacker_id GROUP BY h.hacker_id, h.name ORDER BY count(*) DESC LIMIT 1);
SELECT h.hacker_id, h.name, count(*) as count FROM Hackers h, Challenges c WHERE h.hacker_id = c.hacker_id GROUP BY h.hacker_id, h.name -- Having the max count HAVING count(*) = @max
UNION ALL
SELECT h.hacker_id, h.name, count(*) as count FROM Hackers h, Challenges c WHERE h.hacker_id = c.hacker_id GROUP BY h.hacker_id, h.name HAVING count(*) IN -- a list of challenge counts that = 1 and not max (SELECT a.challengeCounts FROM(SELECT h.hacker_id, h.name, count(*) as challengeCounts FROM Hackers h, Challenges c WHERE h.hacker_id = c.hacker_id GROUP BY h.hacker_id, h.name) a GROUP BY a.challengeCounts -- Having count less than the max count HAVING count() < @max AND count()=1)
ORDER BY count DESC, hacker_id