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
)
,AC_TIME AS(
    SELECT
        CONTEST_ID
        ,PROBLEM_ID
        ,USER_ID
        ,SUBMITTED_AT AS AC_TIME
    FROM TMP
    WHERE STATUS ='AC'
)
,AW_COUNT AS(
    SELECT
        CONTEST_ID
        ,USER_ID
        ,SUM(CASE WHEN STATUS = 'WA' THEN 1 ELSE 0 END) AS WRONG_ANS
    FROM TMP NATURAL JOIN AC_TIME
    WHERE SUBMITTED_AT <= AC_TIME 
    GROUP BY 
        CONTEST_ID
        ,USER_ID
)
SELECT
    RANK() OVER(ORDER BY p.POINT DESC, t.TOTAL_TIME + aw.WRONG_ANS * 300, aw.WRONG_ANS) AS RANK
    ,p.USER_ID
    ,p.POINT
    ,t.TOTAL_TIME + aw.WRONG_ANS * 300 AS EX_TIME
    ,aw.WRONG_ANS
FROM
    TOTAL_POINT P
LEFT OUTER JOIN
    TIME T
        ON P.USER_ID = T.USER_ID
LEFT OUTER JOIN
    AW_COUNT AW
        ON P.USER_ID = AW.USER_ID
WHERE p.CONTEST_ID = 2 AND p.POINT > 0
ORDER BY 1,2