WITH
Calc_MAX
AS
(
    SELECT
         CONTEST_ID
        ,USER_ID
        ,MAX(SUBMITTED_AT) AS MAX_AT
    FROM
    (
        SELECT
             CONTEST_ID
            ,USER_ID
            ,ENTRY_ID
            ,SUBMITTED_AT
            ,STATUS
        FROM SUBMISSIONS
        WHERE ENTRY_ID IS NOT NULL AND STATUS = 'AC'
        ORDER BY
             CONTEST_ID
            ,USER_ID
            ,SUBMITTED_AT
    )
    GROUP BY
         CONTEST_ID
        ,USER_ID
)
,
Make_TABLE
AS
(
    SELECT 
         SUB.*
        ,ENT.*
        ,CM.*
        ,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
    WHERE SUB.ENTRY_ID IS NOT NULL AND SUB.SUBMITTED_AT <= CM.MAX_AT
    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;