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
)
,EX_REC AS(
    SELECT TEMP_REC.*
    FROM TEMP_REC
    INNER JOIN(
        SELECT ENTRY_ID, PROBLEM_ID, MAX(SUBMITTED_AT) AS AC_AT
        FROM TEMP_REC 
        WHERE STATUS = 'AC'
        GROUP BY ENTRY_ID, PROBLEM_ID
    ) A
    ON TEMP_REC.ENTRY_ID = A.ENTRY_ID AND TEMP_REC.PROBLEM_ID = A.PROBLEM_ID
    WHERE TEMP_REC.SUBMITTED_AT <= A.AC_AT
)
,SUM_REC AS(
    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
)
,DIST_USER AS(
    SELECT DISTINCT USER_ID
    FROM ENTRIES
    WHERE
    CONTEST_ID = 2
    AND ENTRY_ID IS NOT NULL
)
SELECT
RANK() OVER(ORDER BY POINT DESC, EX_TIME ASC) AS RANK
,A.*
FROM(
    SELECT 
    DIST_USER.USER_ID
    ,IFNULL(SUM_REC.POINT,0) AS POINT
    ,IFNULL(SUM_REC.EX_TIME,0) AS EX_TIME
    ,IFNULL(SUM_REC.WRONG_ANS,0) AS WRONG_ANS
    FROM DIST_USER
    LEFT JOIN SUM_REC
    ON DIST_USER.USER_ID = SUM_REC.USER_ID
)A
ORDER BY RANK ASC, WRONG_ANS ASC, USER_ID ASC