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. Top Competitors
  5. Discussions

Top Competitors

  • arpit10gupta
     + 0 comments

    Filter out max score per challenege for a hacker guys first. Then is shoud give correct results. For example, if a hacker made 3 submissions, two of which hit the full score, the challenge would be counted twice, leading to incorrect leaderboard rankings.

    **CODE: **

    WITH max_scores AS (
    -- Get the maximum score achieved by each hacker for each challenge
    SELECT hacker_id, challenge_id, MAX(score) AS max_score
    FROM submissions
    GROUP BY hacker_id, challenge_id
),
full_scores AS (
    -- Join with difficulty table to check if the hacker achieved full score
    SELECT ms.hacker_id, h.name, ms.challenge_id
    FROM max_scores ms
    JOIN hackers h ON ms.hacker_id = h.hacker_id
    JOIN challenges c ON ms.challenge_id = c.challenge_id
    JOIN difficulty d ON c.difficulty_level = d.difficulty_level
    WHERE ms.max_score = d.score  -- Full score condition
),
hacker_counts AS (
    -- Count challenges where hackers got full scores
    SELECT hacker_id, name, COUNT(challenge_id) AS challenge_count
    FROM full_scores
    GROUP BY hacker_id, name
)
-- Filter hackers with full scores in more than one challenge
SELECT hacker_id, name
FROM hacker_counts
WHERE challenge_count > 1
ORDER BY challenge_count DESC, hacker_id ASC;