WITH TMP AS(
    SELECT
        s.SUBMIT_ID AS SUBMIT_ID
        ,s.CONTEST_ID AS CONTEST_ID
        ,s.PROBLEM_ID AS PROBLEM_ID
        ,s.USER_ID AS USER_ID
        ,s.ENTRY_ID AS ENTRY_ID
        ,s.SUBMITTED_AT AS SUBMITTED_AT
        ,e.STARTED_AT AS STARTED_AT
        ,s.STATUS AS STATUS
        ,s.POINT AS POINT
    FROM SUBMISSIONS s INNER JOIN ENTRIES e USING(ENTRY_ID)
)
,TOTAL_POINT AS(
    SELECT
        CONTEST_ID
        ,USER_ID
        ,SUM(POINT) AS POINT
    FROM TMP
    WHERE STATUS = 'AC'
    GROUP BY
        CONTEST_ID
        ,USER_ID
)
,TIME AS(
    SELECT
        CONTEST_ID
        ,USER_ID
        ,strftime('%s', MAX(SUBMITTED_AT)) - strftime('%s', STARTED_AT) AS TOTAL_TIME
    FROM TMP
    WHERE STATUS = 'AC'
    GROUP BY
        CONTEST_ID
        ,USER_ID
)
,AW_COUNT AS(
    SELECT
        a.CONTEST_ID
        ,a.USER_ID
        ,SUM(CASE WHEN a.STATUS != 'AC' THEN 1 ELSE 0 END) AS WRONG_ANS
    FROM
        TMP a
    LEFT OUTER JOIN
        (
        SELECT
            USER_ID
            ,CONTEST_ID
            ,PROBLEM_ID
            ,MAX(SUBMITTED_AT) AS SUBMITTED_AT
        FROM TMP
        WHERE STATUS = 'AC'
        GROUP BY
            USER_ID
            ,CONTEST_ID
            ,PROBLEM_ID
        ) b 
    ON a.USER_ID = b.USER_ID
        AND a.CONTEST_ID = b.CONTEST_ID
        AND a.PROBLEM_ID = b.PROBLEM_ID
        AND a.SUBMITTED_AT < b.SUBMITTED_AT
    GROUP BY 
        a.CONTEST_ID
        ,a.USER_ID
)

SELECT
    RANK() OVER(ORDER BY TP.POINT DESC, TOTAL_TIME + SUM(CASE WHEN STATUS != 'AC' THEN 1 ELSE 0 END) * 300) AS RANK
    ,USER_ID
    ,TP.POINT
    ,TOTAL_TIME + SUM(CASE WHEN STATUS != 'AC' THEN 1 ELSE 0 END) * 300 EX_TIME
    ,SUM(CASE WHEN STATUS != 'AC' THEN 1 ELSE 0 END) WRONG_ANS
FROM
    TMP T 
    JOIN TOTAL_POINT tp USING(CONTEST_ID ,USER_ID)
    JOIN TIME ti USING(CONTEST_ID ,USER_ID)
WHERE
    CONTEST_ID = 2 
    AND tp.POINT > 0 
    AND EXISTS
        (SELECT
            1
        FROM TMP 
        WHERE STATUS='AC'
            AND T.CONTEST_ID = CONTEST_ID
            AND T.PROBLEM_ID = PROBLEM_ID
            AND T.USER_ID = T.USER_ID
            AND T.ENTRY_ID = ENTRY_ID 
            AND T.SUBMITTED_AT <= SUBMITTED_AT
        )
GROUP BY USER_ID
ORDER BY 1,5,2