15 Days of Learning SQL

  • + 1 comment
    WITH
    CTE1 AS
    (SELECT submission_date, hacker_id, COUNT(hacker_id) AS hacker_count
    FROM Submissions
    GROUP BY submission_date, hacker_id
    ORDER BY submission_date, hacker_count DESC, hacker_id ASC),
    
    CTE2 AS 
    (SELECT submission_date, hacker_id, hacker_count,
    ROW_NUMBER() OVER(PARTITION BY submission_date ORDER BY hacker_count DESC, hacker_id) AS row_num,
    FROM CTE1
    ORDER BY submission_date, hacker_count DESC, hacker_id ASC),
    
    CTE3 AS
    (SELECT curr.submission_date AS current_date, prev.submission_date AS previous_date, curr.hacker_id
    FROM Submissions AS curr
    LEFT JOIN Submissions AS prev
    ON curr.submission_date - 1 = prev.submission_date AND curr.hacker_id = prev.hacker_id
    WHERE prev.submission_date IS NOT NULL
    ORDER BY curr.submission_date),
    
    CTE4 AS
    (SELECT submission_date, COUNT(DISTINCT hacker_id) FROM Submissions
    WHERE submission_date = '2016-03-01'
    GROUP BY submission_date)
    
    SELECT A1.current_date, A1.CNT, A2.hacker_id, A3.NAME
    FROM 
    (SELECT a.current_date, count(DISTINCT a.hacker_id) CNT
    FROM CTE3 AS a
    GROUP BY a.current_date
    UNION
    SELECT * FROM CTE4) A1
    INNER JOIN 
    (SELECT * FROM CTE2 WHERE ROW_NUM = 1) A2
    ON A1.current_date = A2.submission_date
    INNER JOIN Hackers A3
    ON A2.hacker_id = A3.HACKER_ID;
    

    I get the correct output when tried on Snowflake, but MySQL option on hackerrank shows some error.

    "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 'CTE1 AS (SELECT submission_date, hacker_id, COUNT(hacker_id) AS hacker_count FRO' at line 2"

    Can anyone help me correct this.