Cannot figure out what's wrong with the syntax of WITH in MySQL

Question

The SQL snippet that was written by myself apparently coincides with the syntax specified in the following URLs:

Introduction to MySQL WITH Clause

WITH (Common Table Expressions)

Could you spot what's wrong with the given SQL snippet?

Incidentally, the solution has been tried to apply to the question excerpted from HackerRank - Contest Leaderboard

WITH
MAX_POINT
AS 
(
    SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE)
    FROM SUBMISSIONS
    GROUP BY HACKER_ID, CHALLENGE_ID
),
HACKER_TOTAL_POINTS
AS
(
    SELECT HACKER_ID, SUM(SCORE) AS TOTAL_POINT
    FROM MAX_POINT
    GROUP BY HACKER_ID
    HAVING SUM(SCORE) > 0
)

SELECT T.HACKER_ID, H.NAME,T.TOTAL_POINT
FROM HACKER_TOTAL_POINTS T
INNER JOIN HACKERS H
ON H.HACKER_ID = T.HACKER_ID
ORDER BY T.TOTAL_POINT DESC, T.HACKER_ID;

ERROR 1064 (42000) at line 5: 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 'MAX_POINT
AS 
(
    SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE)
    FROM SUBMISSI' at line 2

Answer 1

1. At first glance, WITH clause in your code looks correct. However, I did not check it in detail, so you shouldn't rely on my answer in this sense.

2. To answer the second part of your question, i.e. why you are getting that error from HackerRank, my understanding is: HackerRank's version of MySQL doesn't support CTEs (i.e. WITH clause). If you run

SELECT Version();

it prints out 5.7.27 . And according to this post, MySQL 5.7 doesn't support CTEs