Mysql syntax error in Hackerrank – Top competitors

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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.