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'
        ,MIN(STARTED_AT) as 'STARTED_AT'
    FROM 
        AC_LIST
    GROUP BY 
        USER_ID
)
, WRONG_ANS AS (
    SELECT
        j1.USER_ID
        ,COUNT(*) as 'WRONG_ANS'
    FROM
        JOINTABLE j1 INNER JOIN JOINTABLE j2 ON
            j1.USER_ID = j2.USER_ID
            AND j2.STATUS <> 'AC'
            AND j1.STATUS = 'AC'
            AND j1.PROBLEM_ID = j2.PROBLEM_ID
    WHERE
        j2.SUBMITTED_AT < j1.SUBMITTED_AT
    GROUP BY
        j1.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', MIN(e.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
        
        INNER JOIN ENTRIES e ON
            a.USER_ID = e.USER_ID
            AND e.CONTEST_ID = 2
            AND e.ENTRY_ID IS NOT NULL
    GROUP BY e.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