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
|
2378 Discussions
|
Please Login in order to post a comment
SELECT h.hacker_id, h.name FROM hackers h inner JOIN submissions s ON h.hacker_id = s.hacker_id inner JOIN challenges c ON s.challenge_id = c.challenge_id inner 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.hacker_id) > 1 ORDER BY COUNT(s.hacker_id) DESC, h.hacker_id ASC;
can u please tell why have we used group by here?
It will ensures that each hacker appears only once in the final output otherwise without doing this hacker's name and hacker'id it will appear the number of times they have score full
need to change to HAVING COUNT(DISTINCT s.challenge_id) > 1, because we count the challenge not the hacker, hacker can submit multiple submission for a challenges
You can't Map hackers and submissions using hacker_id only. One hacker_id will have multiple challenge_Id. It will not give correct score for given hacker and given challenge
SELECT
FROM
JOIN
JOIN
JOIN
WHERE
GROUP BY
HAVING
ORDER BY
20 S.hacker_id ASC;
SELECT S.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 GROUP BY S.hacker_id, H.name HAVING COUNT(S.submission_id) > 1 ORDER BY COUNT(S.submission_id) DESC, hacker_id;
with chall_sub as(SELECT su.hacker_id,su.challenge_id,difficulty_level,max(score) as highscore FROM Submissions su INNER JOIN Challenges ch ON su.challenge_id=ch.challenge_id GROUP BY su.hacker_id,su.challenge_id,difficulty_level)
SELECT Ha.hacker_id,Ha.name FROM Hackers Ha INNER JOIN chall_sub cs ON Ha.hacker_id= cs.hacker_id INNER JOIN Difficulty dif ON cs.difficulty_level= dif.difficulty_level AND cs.highscore=dif.score GROUP BY Ha.hacker_id,Ha.name HAVING COUNT(cs.challenge_id)>1 ORDER BY COUNT(cs.challenge_id) desc,Ha.hacker_id asc
select h.hacker_id, h.name from Hackers h join challenges c on h.hacker_id = c.hacker_id join difficulty d on c.difficulty_level = d.difficulty_level join submissions s on d.score = s.score 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
(what's wrong with this?)