Top Competitors

  • + 0 comments

    Oracle CTE solution

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