WITH EX_REC AS(
    SELECT
    S.*,E.STARTED_AT 
    FROM SUBMISSIONS S
    LEFT JOIN ENTRIES E
    ON S.ENTRY_ID = E.ENTRY_ID
    LEFT JOIN(
    SELECT USER_ID, PROBLEM_ID, MAX(SUBMITTED_AT) AS AC_AT
    FROM SUBMISSIONS 
    WHERE STATUS = 'AC'
    GROUP BY USER_ID, PROBLEM_ID
    ) A
    ON S.USER_ID = A.USER_ID AND S.PROBLEM_ID = A.PROBLEM_ID
    WHERE 
    E.CONTEST_ID = 2 AND 
    E.ENTRY_ID IS NOT NULL AND
    STRFTIME('%s', S.SUBMITTED_AT) - STRFTIME('%s', E.STARTED_AT) <= 3600
    AND (S.ENTRY_ID, S.PROBLEM_ID) in (
    SELECT S.ENTRY_ID, S.PROBLEM_ID FROM SUBMISSIONS WHERE STATUS = 'AC'
    )
    AND S.SUBMITTED_AT <= A.AC_AT
)
SELECT
RANK() OVER(ORDER BY POINT DESC, EX_TIME ASC) AS RANK
,A.*
FROM(
    SELECT 
    USER_ID
    ,SUM(POINT) AS POINT
    ,STRFTIME('%s', MAX(SUBMITTED_AT)) - STRFTIME('%s', MIN(STARTED_AT)) + (SUM(CASE WHEN STATUS='WA' THEN 1 ELSE 0 END) * 300) AS EX_TIME
    ,SUM(CASE WHEN STATUS='WA' THEN 1 ELSE 0 END) AS WRONG_ANS
    FROM EX_REC
    GROUP BY USER_ID
)A
ORDER BY RANK ASC, WRONG_ANS ASC, USER_ID ASC