I'm currently working on solving this SQL question on Hackerrank, accessible via https://www.hackerrank.com/challenges/interviews/problem?isFullScreen=true, using MySQL common table expression. If I just do this, it is fine.
SELECT challenge_id, SUM(total_submissions) AS cid_tot_sub, SUM(total_accepted_submissions) AS cid_tot_acc_sub
FROM Submission_Stats
GROUP BY challenge_id
However, when I attempt it using MySQL common table expressions, I encounter the following error that I don't understand. Could you please advise? Thank you very much.
ERROR 1064 (42000) at line 55: 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 'cte_ss AS (
SELECT challenge_id, SUM(total_submissions) AS cid_tot_sub, SU' at line 2
WITH
cte_ss AS (
SELECT challenge_id, SUM(total_submissions) AS cid_tot_sub, SUM(total_accepted_submissions) AS cid_tot_acc_sub
FROM Submission_Stats
GROUP BY challenge_id
),
cte_vs AS (
SELECT challenge_id, SUM(total_views) AS cid_tot_views, SUM(total_unique_views) AS cid_tot_uniq_views
FROM View_Stats
GROUP BY challenge_id
)
select * from cte_ss;