with rank_cte as (select h.hacker_id,h.name,count(c.challenge_id) as No_challenges,Dense_rank() over(order by count(c.challenge_id) desc) as ranking from Hackers as h inner join challenges as c on h.hacker_id =c.hacker_id group by h.hacker_id,h.name)

select hacker_id,name,No_Challenges from rank_cte where ranking=1 or ranking IN (select ranking from rank_cte where ranking <> 1 group by ranking having count(ranking)=1) order by No_challenges desc,hacker_id

SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS total FROM Hackers h JOIN Challenges c ON h.hacker_id = c.hacker_id GROUP BY h.hacker_id, h.name HAVING COUNT(c.challenge_id) = ( SELECT MAX(challenge_count) FROM ( SELECT COUNT() AS challenge_count FROM Challenges GROUP BY hacker_id ) AS counts ) OR COUNT(c.challenge_id) IN ( SELECT challenge_count FROM ( SELECT COUNT() AS challenge_count FROM Challenges GROUP BY hacker_id ) AS temp GROUP BY challenge_count HAVING COUNT(*) = 1 ) ORDER BY total DESC, h.hacker_id;

Query for SQL Server

DECLARE @result1 TABLE (id int, name nvarchar(200), totalC int)

INSERT INTO @result1 select c.hacker_id id, h.name name, count(challenge_id) tnChallenge from challenges c join hackers h on h.hacker_id = c.hacker_id group by c.hacker_id, h.name

DECLARE @result2 TABLE(total int)

insert into @result2 select max(totalC) from @result1

select * from @result1 r where totalC = (select total from @result2) or ((select count(*) from @result1 r1 where r1.totalC = r.totalC ) = 1 ) order by totalC desc, id

with hack_cte as (select h.hacker_id, h.name, count(c.challenge_id) as total_count, dense_rank() over(order by count(c.challenge_id) desc) as rnk from hackers h join challenges c on h.hacker_id = c.hacker_id group by h.hacker_id, h.name)

select hc.hacker_id, hc.name, hc.total_count from hack_cte hc where hc.rnk = 1 or hc.rnk = any (select hack_cte.rnk from hack_cte where hack_cte.rnk <> 1 group by hack_cte.rnk having count(hack_cte.rnk) = 1) order by hc.total_count desc, hc.hacker_id

Using Oracle:

WITH subs AS
    (
    SELECT h.hacker_id, h.name, COUNT(DISTINCT c.challenge_id) as submissions
    FROM Hackers h
    JOIN Challenges c
        ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id, h.name
        ),        
sub_details AS 
    (SELECT hacker_id, name, submissions,
        MAX(submissions) OVER() AS max_subs,
        COUNT(*) OVER(PARTITION BY submissions) AS iden_sub_num
    FROM subs
    )
SELECT hacker_id, name, submissions
FROM sub_details
WHERE iden_sub_num = 1 OR submissions = max_subs
ORDER BY submissions DESC, hacker_id ASC;