Top Competitors

  • + 5 comments

    While that yields a correct-ish result, a couple of critiques:

    • The spec didn't call for concatenation in the result set, so technically this didn't meet the requirement - but the test let you squeak by.
    • Avoid expressing filtering conditions in JOIN predicate. JOIN predicate should involve key columns only. (Use WHERE to filter for full scores only. score is not a key in Submissions.)

    Alternative solution, a bit more straightforward & easier to read:

    SELECT
    	h.hacker_id
    ,	h.name
    FROM
    	Hackers AS h
    	JOIN Submissions AS s ON s.hacker_id = h.hacker_id
    	JOIN Challenges AS c ON c.challenge_id = s.challenge_id
    	JOIN Difficulty AS d ON d.difficulty_level = c.difficulty_level
    WHERE
    	s.score = d.score
    GROUP BY
    	h.hacker_id
    ,	h.name
    HAVING
    	COUNT(1) > 1
    ORDER BY
    	COUNT(1) DESC
    ,	h.hacker_id ASC
    ;