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
|
2096 Discussions
|
Please Login in order to post a comment
guys help me out with the below query and why the condition score = 100 is not to be used. Many people under discussions have not used that any reasons????
SELECT hackers.hacker_id, hackers.name FROM submissions JOIN hackers ON submissions.hacker_id = hackers.hacker_id JOIN challenges ON submissions.challenge_id = challenges.challenge_id JOIN difficulty ON difficulty.difficulty_level = challenges.difficulty_level AND difficulty.score = submissions.score WHERE submissions.score = 100 GROUP BY hackers.hacker_id, hackers.name HAVING COUNT(submissions.hacker_id) > 1 ORDER BY COUNT(submissions.hacker_id) DESC, hackers.hacker_id ASC;
select h.hacker_id,name from submissions s join hackers h on s.hacker_id=h.hacker_id join challenges c on s.challenge_id=c.challenge_id join difficulty d on c.difficulty_level=d.difficulty_level where d.difficulty_level=c.difficulty_level and s.score=d.score group by h.hacker_id,h.name HAVING COUNT(s.hacker_id) > 1 order by count(s.hacker_id) desc,h.hacker_id asc;
This is clean and appropriate for the medium level basic join Problems
SELECT h.hacker_id, h.name FROM submissions s JOIN challenges c ON s.challenge_id = c.challenge_id JOIN difficulty d ON c.difficulty_level = d.difficulty_level JOIN hackers h ON s.hacker_id = h.hacker_id WHERE s.score = d.score AND c.difficulty_level = d.difficulty_level GROUP BY h.hacker_id, h.name HAVING COUNT(s.hacker_id) > 1 ORDER BY COUNT(s.hacker_id) DESC, s.hacker_id ASC
My SQL:
select s.hacker_id, h.name--, count(challenge_id) as nbr from submissions s join hackers h on h.hacker_id = s.hacker_id where s.submission_id in (select s.submission_id from submissions s join challenges c on c.challenge_id = s.challenge_id join difficulty d on d.difficulty_level = c.difficulty_level
where s.score = d.score) group by s.hacker_id, h.name having count(challenge_id) > 1 order by count(challenge_id) desc, hacker_id asc