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) >= 0 
    AND STRFTIME('%s', S.SUBMITTED_AT) - STRFTIME('%s', E.STARTED_AT) <= 3600 
)
,EX_REC AS(
    SELECT TEMP_REC.*
    FROM (
        SELECT ENTRY_ID, PROBLEM_ID, MAX(SUBMITTED_AT) AS AC_AT
        FROM TEMP_REC 
        WHERE STATUS = 'AC'
        GROUP BY ENTRY_ID, PROBLEM_ID
    ) A
    LEFT JOIN TEMP_REC
    ON TEMP_REC.ENTRY_ID = A.ENTRY_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
WHERE POINT > 0
ORDER BY RANK ASC, WRONG_ANS ASC, USER_ID ASC