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
|
2468 Discussions
|
Please Login in order to post a comment
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
WITH CTE as ( SELECT s.hacker_id as hid,h.name as name,COUNT() as total from Submissions AS s JOIN Challenges AS c ON s.challenge_id = c.challenge_id JOIN Difficulty AS d ON c.difficulty_level = d.difficulty_level JOIN Hackers AS h ON s.hacker_id = h.hacker_id WHERE s.score = d.score GROUP BY s.hacker_id,h.name HAVING COUNT() > 1 )
SELECT hid,name FROM CTE ORDER BY total desc,hid