WITH TEMP_REC AS(
    SELECT
    S.*,E.STARTED_AT
    FROM SUBMISSIONS S
    LEFT JOIN ENTRIES E
    ON S.ENTRY_ID = E.ENTRY_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'
    )
),
EX_REC AS(
    SELECT TEMP_REC.*
    FROM TEMP_REC
    LEFT JOIN(
        SELECT USER_ID, PROBLEM_ID, MAX(SUBMITTED_AT) AS AC_AT
        FROM TEMP_REC 
        WHERE STATUS = 'AC'
        GROUP BY USER_ID, PROBLEM_ID
    ) A
    ON TEMP_REC.USER_ID = A.USER_ID AND TEMP_REC.PROBLEM_ID = A.PROBLEM_ID
    WHERE TEMP_REC.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