WITH JOINTABLE AS (
    SELECT
        e.ENTRY_ID
        ,e.USER_ID
        ,DATETIME(e.STARTED_AT) as 'STARTED_AT'
        ,s.PROBLEM_ID
        ,DATETIME(s.SUBMITTED_AT) as 'SUBMITTED_AT'
        ,s.STATUS
        ,s.POINT
    FROM
        ENTRIES e INNER JOIN
            SUBMISSIONS s ON
                e.USER_ID = s.USER_ID AND 
                e.CONTEST_ID = 2
    WHERE
        -- datetime(s.SUBMITTED_AT) < datetime(e.STARTED_AT, '+60 minutes')
        s.ENTRY_ID IS NOT NULL
            
)
,POINT AS (
    SELECT
        USER_ID
        ,SUM(POINT) as 'POINT'
    FROM
        JOINTABLE
    WHERE
        STATUS = 'AC'
    GROUP BY
        USER_ID
)
,AC_LIST AS (
    SELECT 
        USER_ID
        ,PROBLEM_ID
        ,SUBMITTED_AT
        ,STARTED_AT
    FROM 
        JOINTABLE
    WHERE 
        STATUS = 'AC'
)
, LAST_AC AS (
    SELECT 
        USER_ID
        ,MAX(SUBMITTED_AT) as 'LAST_AC'
        ,STARTED_AT
    FROM 
        AC_LIST
    GROUP BY 
        USER_ID
)
, WRONG_ANS AS (
    SELECT
        j.USER_ID
        ,COUNT(j.SUBMITTED_AT) as 'WRONG_ANS'
    FROM 
        JOINTABLE j INNER JOIN AC_LIST a
            ON j.USER_ID = a.USER_ID AND
            j.PROBLEM_ID = a.PROBLEM_ID
    WHERE
        j.STATUS <> 'AC'
        AND j.SUBMITTED_AT < a.SUBMITTED_AT
        AND j.PROBLEM_ID IN (SELECT PROBLEM_ID FROM AC_LIST WHERE USER_ID = j.USER_ID)
    GROUP BY
        j.USER_ID
),
TMP AS (
 SELECT
        a.USER_ID as 'USER_ID'
        ,p.POINT as 'POINT'
        ,strftime('%s', l.LAST_AC) - strftime('%s', l.STARTED_AT) + 300 * IFNULL(w.WRONG_ANS, 0) as 'EX_TIME'
        ,IFNULL(w.WRONG_ANS, 0) as 'WRONG_ANS'
    FROM
         (
            POINT p LEFT OUTER JOIN LAST_AC l ON
                p.USER_ID = l.USER_ID
        ) a LEFT OUTER JOIN WRONG_ANS w ON
            a.USER_ID = w.USER_ID
)
SELECT
    RANK() OVER (ORDER BY POINT DESC, EX_TIME ASC) as 'RANK'
    ,USER_ID
    ,POINT
    ,EX_TIME
    ,WRONG_ANS
FROM 
    TMP
WHERE
    POINT <> 0
ORDER BY
    RANK ASC, WRONG_ANS ASC, USER_ID ASC
    
-- SELECT
--     *
-- FROM 
--     TMP
-- SELECT
--     *
-- FROM
--     POINT
-- SELECT
--     *
-- FROM
--     WRONG_ANS
    
-- SELECT
--     *
-- FROM
--     SUBMISSIONS
-- SELECT
--     *
-- FROM
--     JOINTABLE
-- SELECT
--     *
-- FROM
--     LAST_AC