We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Basic Join
  4. Challenges
  5. Discussions

Challenges

  • jd_caicedo1008
     + 0 comments

    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
    );