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')
            
)
,POINT AS (
    SELECT
        USER_ID
        ,SUM(POINT) as 'POINT'
    FROM
        JOINTABLE
    WHERE
        STATUS = 'AC'
    GROUP BY
        USER_ID
)
, LAST_AC AS (
    SELECT 
        USER_ID
        ,MAX(SUBMITTED_AT) as 'LAST_AC'
        ,STARTED_AT
    FROM 
        JOINTABLE
    WHERE 
        STATUS = 'AC'
    GROUP BY 
        USER_ID
)
, WRONG_ANS AS (
    SELECT
        j.USER_ID
        ,COUNT(j.SUBMITTED_AT) as 'WRONG_ANS'
    FROM 
        JOINTABLE j INNER JOIN LAST_AC a
            ON j.USER_ID = a.USER_ID
    WHERE
        j.STATUS = 'WA'
        AND j.SUBMITTED_AT < a.LAST_AC
    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 * w.WRONG_ANS as 'EX_TIME'
        ,w.WRONG_ANS as 'WRONG_ANS'
    FROM
        POINT p INNER JOIN (
            LAST_AC l INNER JOIN WRONG_ANS w ON
                w.USER_ID = l.USER_ID
        ) a ON
            a.USER_ID = p.USER_ID
)


SELECT
    RANK() OVER (ORDER BY EX_TIME ASC) as 'RANK'
    ,USER_ID
    ,POINT
    ,EX_TIME
    ,WRONG_ANS
FROM 
    TMP

-- SELECT
--     *
-- FROM 
--     TMP


-- SELECT
--     *
-- FROM
--     POINT

-- SELECT
--     *
-- FROM
--     WRONG_ANS

-- SELECT
--     *
-- FROM
--     SUBMISSIONS

-- SELECT
--     *
-- FROM
--     JOINTABLE

-- SELECT
--     *
-- FROM
--     LAST_AC