SELECT h.hacker_id, h.name, counts.total FROM Hackers h JOIN ( SELECT hacker_id, COUNT() AS total FROM Challenges GROUP BY hacker_id ) AS counts ON h.hacker_id = counts.hacker_id WHERE counts.total = ( SELECT MAX(ch_count) FROM ( SELECT COUNT() AS ch_count FROM Challenges GROUP BY hacker_id ) AS max_counts ) OR counts.total IN ( SELECT ch_count FROM ( SELECT COUNT() AS ch_count FROM Challenges GROUP BY hacker_id ) AS all_counts GROUP BY ch_count HAVING COUNT() = 1 ) ORDER BY counts.total DESC, h.hacker_id ASC;

select h.hacker_id, h.name , cc.total FROM Hackers h JOIN (Select hacker_id, count() as total from Challenges Group By hacker_id ) as cc ON h.hacker_id = cc.hacker_id Where cc.total = (SELECT MAX(count_challenge) FROM ( select hacker_id, count() as count_challenge from Challenges Group By hacker_id ) as abc ) OR cc.total in (SELECT count_challenge FROM ( select hacker_id, count() as count_challenge from Challenges Group By hacker_id ) as bcd group by count_challenge having count() = 1 ) ORDER BY cc.total DESC, h.hacker_id ASC

with ch_count as ( select h.hacker_id , h.name, count(c.challenge_id) as c_count from Challenges c join Hackers h on c.hacker_id = h.hacker_id group by h.hacker_id, h.name ), max_count as ( select max(c_count) as max_counts from ch_count )

select ch.hacker_id , ch.name, ch.c_count from ch_count ch where ch.c_count = ( select max_counts from max_count) or ch.c_count Not In ( select c_count from ch_count group by c_count having count(*) > 1 and c_count < (select max_counts from max_count) )

order by ch.c_count desc, hacker_id asc

with chall_count as( select hackers.hacker_id, hackers.name, count(challenges.challenge_id) tot_challenges from hackers join challenges on hackers.hacker_id = challenges.hacker_id group by 1,2)

select * from chall_count where tot_challenges not in (select tot_challenges from chall_count where tot_challenges <> (select max(tot_challenges) from chall_count) group by tot_challenges having count(*)>1) order by tot_challenges desc,hacker_id;

with cnt_chlg as ( select hacker_id, count() as cnt from challenges group by hacker_id ) ,cnt_rank as ( select hacker_id,cnt, rank() over(order by cnt desc) as rnk from cnt_chlg ) ,drop_rnk as( select cr.hacker_id,cr.cnt,cr.rnk,a.rnk_cnt from cnt_rank cr join(select cnt,rnk,count() as rnk_cnt from cnt_rank group by cnt,rnk )a on a.cnt=cr.cnt )

select h.hacker_id,h.name,dr.cnt as challenges_created from hackers h join drop_rnk dr on dr.hacker_id=h.hacker_id where (dr.rnk=1) or (dr.rnk>1 and dr.rnk_cnt=1) order by dr.cnt desc