WITH
Calc_MAX_SUB
AS
(
    SELECT
         CONTEST_ID
        ,USER_ID
        ,PROBLEM_ID
        ,ENTRY_ID
        ,SUBMITTED_AT
        ,STATUS
    FROM SUBMISSIONS
    WHERE ENTRY_ID IS NOT NULL AND STATUS = 'AC'
    ORDER BY
         CONTEST_ID
        ,USER_ID
        ,PROBLEM_ID
        ,SUBMITTED_AT
)
,
Calc_MAX
AS
(
    SELECT
         CONTEST_ID
        ,USER_ID
        ,MAX(SUBMITTED_AT) AS MAX_AT
    FROM Calc_MAX_SUB
    GROUP BY
         CONTEST_ID
        ,USER_ID
)
,
Make_TABLE
AS
(
    SELECT 
         SUB.*
        ,ENT.*
        ,CM.*
        ,MS.*
        ,CASE
            WHEN SUB.STATUS = 'AC' AND SUB.SUBMITTED_AT =  CM.MAX_AT THEN STRFTIME('%s',SUB.SUBMITTED_AT) - STRFTIME('%s',ENT.STARTED_AT) 
            WHEN SUB.STATUS = 'AC' AND SUB.SUBMITTED_AT <> CM.MAX_AT THEN 0 
            ELSE 300 END AS EXAM_AT
    FROM SUBMISSIONS AS SUB
    LEFT JOIN ENTRIES AS ENT on SUB.CONTEST_ID = ENT.CONTEST_ID AND SUB.USER_ID = ENT.USER_ID AND SUB.ENTRY_ID = ENT.ENTRY_ID  
    LEFT JOIN Calc_MAX AS CM on SUB.CONTEST_ID = CM.CONTEST_ID AND SUB.USER_ID = CM.USER_ID
    LEFT JOIN Calc_MAX_SUB AS MS on SUB.CONTEST_ID = MS.CONTEST_ID AND SUB.USER_ID = MS.USER_ID AND SUB.PROBLEM_ID = MS.PROBLEM_ID
    WHERE SUB.ENTRY_ID IS NOT NULL AND SUB.SUBMITTED_AT <= CM.MAX_AT AND MS.PROBLEM_ID IS NOT NULL
    ORDER BY
         SUB.CONTEST_ID
        ,SUB.USER_ID
        ,SUB.PROBLEM_ID
        ,SUB.SUBMITTED_AT
)
,
Ranking_TABLE
AS
(
    SELECT
         RANK() OVER (ORDER BY POINT DESC, EX_TIME, WRONG_ANS) AS RANK
        ,USER_ID
        ,POINT
        ,EX_TIME
        ,WRONG_ANS
    FROM
    (
        SELECT
             USER_ID
            ,SUM(POINT) AS POINT
            ,SUM(EXAM_AT) AS EX_TIME
            ,SUM(CASE WHEN STATUS = 'AC' THEN 0 ELSE 1 END) AS WRONG_ANS
        FROM Make_TABLE
        WHERE CONTEST_ID = 2
        GROUP BY
             CONTEST_ID
            ,USER_ID
    )
)
SELECT * FROM Ranking_TABLE;