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.
- Prepare
- SQL
- Basic Join
- Top Competitors
- Discussions
Top Competitors
Top Competitors
Sort by
recency
|
2469 Discussions
|
Please Login in order to post a comment
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
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;
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
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;
SELECT h.hacker_id, h.name