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
- Contest Leaderboard
- Discussions
Contest Leaderboard
Contest Leaderboard
Sort by
recency
|
1979 Discussions
|
Please Login in order to post a comment
My SQL
select t.hacker_id,h.name, sum(t.max_scores) from( SELECT HACKER_ID, challenge_id,max(score) as max_scores from submissions group by HACKER_ID,challenge_id ) t JOIN hackers h on t.hacker_id=h.hacker_id group by t.hacker_id,h.name having sum(t.max_scores) <> 0 order by sum(t.max_scores) desc, t.hacker_id;
MySQL:
anyone who could tell me why this is giving a syntax error with cte as ( select hacker_id, max(score) from Submissions group by hacker_id, challenge_id )
select h.hacker_id, h.name, sum(s.score) from Hackers h join cte s on h.hacker_id=s.hacker_id group by h.hacker_id, h.name having sum(s.score)!=0 order by sum(s.score) desc ,h.hacker_id asc
SELECT h.hacker_id, h.name, SUM(max_scores.max_score) AS total_score FROM hackers h JOIN ( SELECT s.hacker_id, s.challenge_id, MAX(s.score) AS max_score FROM submissions s GROUP BY s.hacker_id, s.challenge_id ) AS max_scores ON h.hacker_id = max_scores.hacker_id GROUP BY h.hacker_id, h.name HAVING SUM(max_scores.max_score) > 0 ORDER BY total_score DESC, h.hacker_id ASC;