Top Competitors

Sort by

recency

|

2469 Discussions

|

  • + 0 comments

    this problem is tricky and waste of time, make sure, you know that you need hacker_id to find submission_id first to get score, then use challenge id to get the full score with difficulty level; not the other way around..

    SELECT h.hacker_id, name FROM hackers AS h join submissions AS s ON h.hacker_id = s.hacker_id join challenges AS c ON c.challenge_id = s.challenge_id join difficulty AS d ON d.difficulty_level = c.difficulty_level WHERE d.score = s.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

    select s.hacker_id,--d.score, c.difficulty_level, h.name from Submissions s inner join Challenges c on s.challenge_id = c.challenge_id inner join difficulty d on d.difficulty_level = c.difficulty_level inner join hackers h on h.hacker_id = s.hacker_id where d.score = s.score --and s.hacker_id = 10857 group by s.hacker_id, h.name having count()>1 order by count() desc, s.hacker_id asc;

  • + 0 comments

    select A.hacker_id, B.name from Submissions A Inner Join Hackers B on A.hacker_id = B.hacker_id INNER JOIN Challenges C ON A.challenge_id = C.challenge_id INNER JOIN Difficulty D ON C.difficulty_level = D.difficulty_level Where A.score = D.score Group by A.hacker_id, B.name having count() > 1 order by COUNT() DESC, A.hacker_id ASC


  • + 0 comments

    Oracle

    select x.HACKER_ID,x.name from hackers x right join (select * from (select HACKER_ID,count(distinct(CHALLENGE_ID)) b from (select s.HACKER_ID,s.CHALLENGE_ID from submissions s where CHALLENGE_ID in (select p.CHALLENGE_ID from (select c.CHALLENGE_ID,d.score from challenges c join difficulty d on c.difficulty_level = d.difficulty_level ) p where p.CHALLENGE_ID = s.CHALLENGE_ID and p.score = s.score)) group BY HACKER_ID order by 2 desc,1 asc) where b <> 1) c on x.HACKER_ID = c.HACKER_ID order by b desc,1;

  • + 1 comment

    SELECT h.hacker_id, h.name

    FROM Submissions as s
    JOIN Hackers as h
        ON s.hacker_id = h.hacker_id
    JOIN Challenges as c
        ON s.challenge_id = c.challenge_id
    JOIN Difficulty as 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