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.
select c.hacker_id, h.name ,count(c.challenge_id) as c_count
/* this is the join we want to output them from */
from Hackers as h
inner join Challenges as c on c.hacker_id = h.hacker_id
/* after they have been grouped by hacker */
group by h.hacker_id, h.name
/* but we want to be selective about which hackers we output /
/ having is required (instead of where) for filtering on groups */
having
/* output anyone with a count that is equal to... */
c_count =
/* the max count that anyone has */
(SELECT MAX(temp1.cnt)
from (SELECT COUNT(challenge_id) as cnt
from Challenges
group by hacker_id) temp1)
/* or anyone who's count is in... */
or c_count in
/* the set of counts... */
(select t.cnt
from (select hacker_id, count(*) as cnt
from challenges
group by hacker_id) t
/* who's group of counts... */
group by t.cnt
/* has only one element */
having count(t.cnt) = 1)
/* finally, the order the rows should be output */
order by c_count DESC, c.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 →
Corrected One
/* these are the columns we want to output */
select c.hacker_id, h.name ,count(c.challenge_id) as c_count
/* this is the join we want to output them from */ from Hackers as h inner join Challenges as c on c.hacker_id = h.hacker_id
/* after they have been grouped by hacker */ group by h.hacker_id, h.name
/* but we want to be selective about which hackers we output / / having is required (instead of where) for filtering on groups */
having
/* finally, the order the rows should be output */ order by c_count DESC, c.hacker_id
/* ;) */ ;