HackerRank Challenges problem solution

In this HackerRank Challenges problem solution, Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Problem solution MS SQL.

select hacker_id,count(*) cll into #tbl
 from Challenges group by hacker_id;

declare @maxi int= (select max(cll) from #tbl);

select h.hacker_id,h.name,c.cll from Hackers h 
inner join #tbl c on h.hacker_id =c.hacker_id 
where c.cll not in (select cll from (select hacker_id,cll,ROW_NUMBER() OVER(PARTITION BY cll ORDER BY cll DESC)  rn from #tbl i) q
where rn=2 and q.cll!=@maxi)  

order by cll desc

drop table #tbl

Problem solution in Oracle.

with maxc as (select max(nbc) as nbc from (select count(*) as nbc from challenges c group by hacker_id)),
maxn as (select nbc, count(*) as nbf from (select count(*) as nbc from challenges c group by hacker_id) group by nbc),
stud as (select h.hacker_id, h.name, count(*) as nbc from hackers h, challenges c where h.hacker_id = c.hacker_id group by h.hacker_id, h.name)
select stud.hacker_id, stud.name, stud.nbc
  from stud, maxc, maxn
 where stud.nbc = maxn.nbc
   and (stud.nbc = maxc.nbc or maxn.nbf = 1)
order by stud.nbc desc, stud.hacker_id;

Problem solution in DB2.

SELECT H.HACKER_ID,
       H.NAME,
       COUNT(C.CHALLENGE_ID) AS TOTAL
FROM HACKERS H,
     CHALLENGES C
WHERE H.HACKER_ID=C.HACKER_ID
GROUP BY H.HACKER_ID,
         H.NAME
HAVING COUNT(C.CHALLENGE_ID) IN
  (SELECT MAX(TOTAL)
   FROM
     (SELECT COUNT(*) AS TOTAL
      FROM CHALLENGES
      GROUP BY HACKER_ID))
OR COUNT(C.CHALLENGE_ID) IN
  (SELECT TOTAL
   FROM
     (SELECT COUNT(*) AS TOTAL
      FROM CHALLENGES
      GROUP BY HACKER_ID)
   GROUP BY TOTAL
   HAVING COUNT(TOTAL)=1)
ORDER BY COUNT(C.CHALLENGE_ID) DESC, H.HACKER_ID;