SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS total_challenges 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(ch_count) FROM ( SELECT COUNT() AS ch_count FROM Challenges GROUP BY hacker_id ) t ) OR COUNT(c.challenge_id) IN ( SELECT ch_count FROM ( SELECT COUNT() AS ch_count FROM Challenges GROUP BY hacker_id ) t GROUP BY ch_count HAVING COUNT(*) = 1 ) ORDER BY total_challenges DESC, h.hacker_id;

SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS total_challenges 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(cnt) FROM ( SELECT COUNT() AS cnt FROM Challenges GROUP BY hacker_id ) ) OR COUNT(c.challenge_id) IN ( SELECT cnt FROM ( SELECT COUNT() AS cnt FROM Challenges GROUP BY hacker_id ) GROUP BY cnt HAVING COUNT(*) = 1 ) ORDER BY total_challenges DESC, h.hacker_id;

mysql -- define max_challenges to exclude from refuse_criteria with max_challenges as ( select max(total_challenges) max_challenge_count from ( select hacker_id, count(challenge_id) total_challenges from challenges group by 1)t )

-- turns challenge_count into a dimension to create the refuse_criteria (excludes the max_one), can simplify this by using row_number and excluding the 1st row as well , refuse_criteria as ( select challenge_count dim_challenge_count, count(hacker_id) hackers from ( select hacker_id, count(challenge_id) challenge_count from challenges group by 1 ) t where challenge_count < (select max_challenge_count from max_challenges) group by 1 having count(hacker_id) > 1 )

-- given refuse_criteria, now we do something similar, but filter in only refused_hacker_ids , refused_hacker_ids as ( select hacker_id from ( select hacker_id, count(challenge_id) challenge_count from challenges group by 1 ) t inner join refuse_criteria rc on rc.dim_challenge_count = t.challenge_count )

-- final query with refused_hacker_ids.hacker_id is null select h.hacker_id, h.name, count(c.challenge_id) challenges_created from hackers h left join refused_hacker_ids r on r.hacker_id = h.hacker_id inner join challenges c on c.hacker_id = h.hacker_id where r.hacker_id is null group by 1,2 order by 3 desc, 1 asc

MySQL:

 WITH top_challenges AS (
    -- Objetivo: Contar la cantidad de retos creados por cada hacker (incluye hackers con 0 retos)
    SELECT
        hac.hacker_id,
        hac.name,
        COUNT(cha.challenge_id) AS quantity_challenge
    FROM hackers hac
    LEFT JOIN challenges cha ON hac.hacker_id = cha.hacker_id
    GROUP BY hac.hacker_id, hac.name
),
mark AS (
    -- Objetivo: Para cada hacker, calcular:
    --  quantity_equal_challenges: cuantos hackers tienen el mismo numero de retos
    --  max_quantity_challenge: el maximo numero de retos creado por cualquier hacker
    SELECT
        *,
        COUNT(*) OVER (PARTITION BY quantity_challenge) AS quantity_equal_challenges,
        MAX(quantity_challenge) OVER() AS max_quantity_challenge
    FROM top_challenges
)

-- Filtra hackers que:
--  tienen el maximo numero de retos, o
--  tienen un numero unico de retos (sin empates)
SELECT
    hacker_id,
    name,
    quantity_challenge
FROM mark
WHERE quantity_equal_challenges = 1
   OR quantity_challenge = max_quantity_challenge
ORDER BY quantity_challenge DESC, hacker_id ASC;