With Joined as(Select s.hacker_id, s.score as _score, difficulty_level, s.challenge_id from Submissions s INNER JOIN Challenges ch ON s.challenge_id = ch.challenge_id),
Joined_2 as (Select j.hacker_id, COUNT(DISTINCT j.challenge_id) as cnt from Joined j INNER JOIN Difficulty d ON j.difficulty_level = d.difficulty_level AND _score = score group by hacker_Id)
Select h.hacker_id, name from Joined_2 j INNER JOIN Hackers h ON j.hacker_id = h.hacker_id order by cnt desc, hacker_id asc;
Here's the link to the problem: Top competitors
Error message:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Joined as(Select s.hacker_id, s.score as _score, difficulty_level, s.challenge_i' at line 1
CodePudding user response:
Hackerrank's mysql version is old; too old to support CTEs.
Use subqueries instead:
Select h.hacker_id, name
from (
Select j.hacker_id, COUNT(DISTINCT j.challenge_id) as cnt
from (
Select s.hacker_id, s.score as _score, difficulty_level, s.challenge_id
from Submissions s
INNER JOIN Challenges ch ON s.challenge_id = ch.challenge_id) j
INNER JOIN Difficulty d ON j.difficulty_level = d.difficulty_level
AND _score = score group by hacker_Id) j2
INNER JOIN Hackers h ON j2.hacker_id = h.hacker_id) x
order by cnt desc, hacker_id asc
Disclaimer: Didn't check for errant commas or brackets etc, but I hope you get the idea.