The main idea was first to do CTE with challenges by hackers and add a column 'duplicates' using a window function to count how many hacker_ids have the same challenge count.

With this CTE, the next step was easy beucase I just filterd by those who doesn't have duplicates, but if they do, only return those that have the max(challenge) count.

WITH challenges_by_hacker AS (
    SELECT Hackers.hacker_id AS hacker_id, 
    name, 
    COUNT(challenge_id) AS num_challenges, 
    COUNT(COUNT(challenge_id)) OVER (PARTITION BY COUNT(challenge_id)) AS duplicates 
    FROM Hackers
    JOIN 
    Challenges ON Hackers.hacker_id = Challenges.hacker_id
    GROUP BY Hackers.hacker_id, name
    ORDER BY num_challenges DESC, Hackers.hacker_id
)
SELECT hacker_id, name, num_challenges
FROM challenges_by_hacker
WHERE duplicates = 1 
    OR 
    num_challenges = (
        SELECT MAX(num_challenges) FROM challenges_by_hacker
    );

MySql :

-- the first Cte Returns all hackers including those who made duplicated challenges and less than the maximum number of challenges

WITH all_hackers AS(SELECT c.hacker_id,h.name,COUNT(*) AS total_challenges FROM Challenges c JOIN Hackers h ON c.hacker_id=h.hacker_id GROUP BY c.hacker_id,h.name ORDER BY total_challenges DESC),

-- this Cte returns duplicated total challenges that less than the maximum number of challenges

duplicated_challenges AS(SELECT total_challenges FROM all_hackers WHERE total_challenges < (SELECT MAX(total_challenges) FROM all_hackers) GROUP BY total_challenges HAVING COUNT(*) >1 )

--- the final query returns hackers without those who made the same number of challenges but less than Max SELECT * FROM all_hackers WHERE total_challenges NOT IN(SELECT total_challenges FROM duplicated_challenges) ORDER BY total_challenges DESC,hacker_id

SELECT p.hacker_id, p.name, p.chlcnt FROM (SELECT hc.hacker_id, hc.name, CASE WHEN hckcnt.hacker_cnt>1 AND ch.challenge_count<50 THEN NULL ELSE ch.challenge_count END AS chlcnt FROM Hackers hc JOIN (SELECT hacker_id, COUNT(challenge_id) AS challenge_count FROM Challenges GROUP BY hacker_id) ch ON ch.hacker_id=hc.hacker_id LEFT JOIN (SELECT t.challenge_count,COUNT(t.hacker_id) AS hacker_cnt FROM (SELECT hc.hacker_id, hc.name, ch.challenge_count FROM Hackers hc JOIN (SELECT hacker_id, COUNT(challenge_id) AS challenge_count FROM Challenges GROUP BY hacker_id) ch ON ch.hacker_id=hc.hacker_id) t GROUP BY t.challenge_count) hckcnt ON hckcnt.challenge_count = ch.challenge_count) p WHERE p.chlcnt IS NOT NULL ORDER BY p.chlcnt DESC, p.hacker_id

SQL server : With A1 as (select H.hacker_id,H.name,count(Challenge_id)cnt from Hackers H join Challenges C on H.hacker_id=C.Hacker_id group by H.hacker_id,H.name), A2 as (select *,dense_rank() over(order by cnt desc) rnk, lag(cnt) over(order by cnt desc) lgcnt,lead(cnt) over(order by cnt desc) ldcnt from A1) ,A3 as (select Hacker_id,name,cnt from A2 where rnk=1 union select Hacker_id,name,cnt from A2 where rnk>1 and case when lgcnt=cnt or ldcnt=cnt then 'no' else 'yes' end='yes' ) select * from A3 order by 3 desc;

select h.hacker_id, h.name, count(c.challenge_id) as total_challenges from Hackers as h join Challenges as c on c.hacker_id = h.hacker_id group by h.hacker_id, h.name having total_challenges = ( select max(total) as max_total from ( select count() as total from Challenges group by hacker_id) as t1 ) or total_challenges in ( select cnt from ( select count() as cnt from Challenges group by hacker_id ) as t2 group by cnt having count(*) = 1 ) order by total_challenges desc, h.hacker_id asc;