Top Competitors

Sort by

recency

|

2358 Discussions

|

  • + 0 comments
    SELECT 
         H.Hacker_id,H.name
    FROM 
        Hackers H 
    JOIN 
        Submissions S ON H.Hacker_id = S.Hacker_id 
    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 
        H.hacker_id, H.name
    HAVING  
        COUNT(DISTINCT S.challenge_id) > 1
    ORDER BY 
        COUNT (DISTINCT S.challenge_id) DESC,
        H.hacker_id ASC;
    
  • + 0 comments

    Oracle:

    SELECT h.hacker_id, h.NAME FROM submissions s JOIN challenges c ON s.challenge_id = c.challenge_id JOIN hackers h ON s.hacker_id = h.hacker_id JOIN difficulty d ON c.difficulty_level = d.difficulty_level WHERE s.score = d.score GROUP BY h.hacker_id, h.NAME HAVING Count(s.challenge_id) > 1 ORDER BY count(s.challenge_id) DESC, h.hacker_id ;

  • + 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;
    
  • + 1 comment

    I think it's worth to mention that, there is just one Submission per Hacker, for a Challenge...

    I initially thought that, Submissions might be recording all submissions made by a Hacker, correct or incorrect.

    Quite intrincate this challenge, I dare to say...

  • + 0 comments

    I think data is wrong. Becouse Challenges table does not match with submissions table on hacker_id and challenge_id.