with totals as ( select h.hacker_id, h.name, count(c.challenge_id) as total from hackers h join challenges c on c.hacker_id = h.hacker_id group by h.hacker_id, h.name order by count(c.challenge_id) desc, c.hacker_id )

select * from totals where total = (select max(total) from totals) or total in (select total from totals group by total having count(total) = 1);

Cleanest way I came up with doing it: WITH CTE AS( select hack.hacker_id, name, (select count(challenge_id) from challenges c where c.hacker_id= hack.hacker_id) as 'chal' from hackers hack )select * from CTE orig where NOT EXISTS (select 1 from CTE new where new.chal=orig.chal and new.hacker_id <> orig.hacker_id) or chal = (select MAX(chal) from CTE) order by chal desc, hacker_id asc

WITH df AS (SELECT t1.hacker_id, t1.name, t1.total_num_challege, COUNT(t1.hacker_id) OVER(PARTITION BY total_num_challege) AS count_num, MAX(total_num_challege) OVER() AS max_num FROM
(SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS total_num_challege FROM Hackers AS h LEFT JOIN Challenges AS c ON c.hacker_id = h.hacker_id GROUP BY h.hacker_id, h.name) as t1 ) SELECT df.hacker_id, df.name, df.total_num_challege FROM df WHERE df.count_num = 1 OR df.total_num_challege = df.max_num ORDER BY df.total_num_challege DESC, hacker_id ASC;

with cte as( select h.hacker_id,name, count(challenge_id) challenges_created, dense_rank() over(order by count(challenge_id) desc) as rnk, COUNT(*) OVER (PARTITION BY count(challenge_id)) cnt from Hackers as h join Challenges as ch on h.hacker_id=ch.hacker_id group by h.hacker_id,name ) select hacker_id,name,challenges_created from cte where rnk =1 or (rnk>1 and cnt=1) order by challenges_created desc,hacker_id

with cte1 as ( select a.hacker_id, a.name, count(1) maxcnt from hackers a inner join challenges b on a.hacker_id = b.hacker_id group by a.hacker_id, name), cte2 as ( select hacker_id, name, maxcnt , dense_rank() over (order by maxcnt desc) seq, count(*) over (partition by maxcnt) cnt from cte1 order by maxcnt desc, hacker_id) select hacker_id, name, maxcnt from cte2 where (seq = 1 or (seq > 1 and cnt = 1))