Top Competitors

  • + 0 comments

    Hey, I created this solution, I am getting error with the CTE syntax, but can't see the mistake, can anyone point me in the right direction?

    WITH CTE_merge AS
    (
    SELECT d.score,
            c.challenge_id,
            c.hacker_id
    FROM challenges c INNER JOIN difficulty d
        ON c.difficulty_level=d.difficulty_level
    )
    SELECT h.hacker_id,
        h.name
    FROM hacker_id h INNER JOIN submissions s
        ON h.hacker_id=s.hacker_id
        INNER JOIN CTE_merge ct 
        ON s.challenge_id=ct.challenge_id AND s.score=ct.score
    GROUP BY h.hacker_id, h.hacker_name
    HAVING COUNT(s.challenge_id)>1
    ORDER BY COUNT(s.challenge_id) DESC, h.hacker_id