Challenges

  • + 5 comments

    I arrived at essentially the same solution, only instead of your method of counting challenges of hackers found in the Challenges table without a join (which is certainly a clean way to do it), I reused the grouping from the original join—to compare the initially derived count to the counts that are unique—in the second portion of the HAVING/OR statement.

    Same cat differently skinned. Sharing since it is more explicit (though admittedly not as efficient) in the hopes it might help anyone stuck on how to arrive at the set of unique counts to include (after including max count achieving hackers).

    Note: The DISTINCT clause and c_unique alias are both unnecessary, but are there to keep me sane.

    SELECT h.hacker_id, 
           h.name, 
           COUNT(c.challenge_id) AS c_count
    FROM Hackers h
    JOIN Challenges c ON c.hacker_id = h.hacker_id
    GROUP BY h.hacker_id, h.name
    HAVING c_count = 
        (SELECT COUNT(c2.challenge_id) AS c_max
         FROM challenges as c2 
         GROUP BY c2.hacker_id 
         ORDER BY c_max DESC limit 1)
    OR c_count IN 
        (SELECT DISTINCT c_compare AS c_unique
         FROM (SELECT h2.hacker_id, 
                      h2.name, 
                      COUNT(challenge_id) AS c_compare
               FROM Hackers h2
               JOIN Challenges c ON c.hacker_id = h2.hacker_id
               GROUP BY h2.hacker_id, h2.name) counts
         GROUP BY c_compare
         HAVING COUNT(c_compare) = 1)
    ORDER BY c_count DESC, h.hacker_id;
    

    Solution is MySQL specific, but easily portable.