Top Competitors

  • + 0 comments
    WITH submissions_clean AS (
        SELECT distinct submission_id,
        hacker_id,
        challenge_id,
        score
        FROM Submissions
        )    
    SELECT s.hacker_id,
        h.name
    FROM submissions_clean s
    JOIN Challenges c
    ON s.challenge_id = c.challenge_id
    JOIN Difficulty d
    ON c.difficulty_level = d.difficulty_level
    JOIN Hackers h
    ON h.hacker_id = s.hacker_id
    WHERE d.score = s.score
    GROUP BY s.hacker_id, h.name
    HAVING COUNT(*) >1
    ORDER BY COUNT(*) desc, s.hacker_id asc