WITH tbl_w AS (
SELECT
    tbl_s.ENTRY_ID
    ,COUNT(tbl_s.SUBMIT_ID) AS WRONG_ANS 
FROM
    SUBMISSIONS AS tbl_s 
INNER JOIN ( 
    SELECT
        *
        ,MAX(SUBMITTED_AT) AS LAST_SUBMITTED_AT 
    FROM
        SUBMISSIONS 
    WHERE
        STATUS = 'AC' 
        AND ENTRY_ID IS NOT NULL 
        AND CONTEST_ID = 2 
    GROUP BY
        ENTRY_ID
        ,PROBLEM_ID) AS SUB_AC 
ON
    tbl_s.ENTRY_ID = SUB_AC.ENTRY_ID 
    AND tbl_s.PROBLEM_ID = SUB_AC.PROBLEM_ID 
    AND tbl_s.SUBMITTED_AT < SUB_AC.LAST_SUBMITTED_AT 
GROUP BY
    tbl_s.ENTRY_ID
),
tbl_point AS (SELECT
    ENTRY_ID
    ,SUM(POINT) AS POINT
    ,MAX(SUBMITTED_AT) AS LAST_SUBMITTED_AT
FROM
    SUBMISSIONS AS tbl_s
WHERE
    STATUS = 'AC' 
    AND ENTRY_ID IS NOT NULL 
    AND CONTEST_ID = 2 
GROUP BY
    ENTRY_ID
) 
SELECT
    RANK() OVER (ORDER BY POINT DESC, STRFTIME('%s', LAST_SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) + 5 * 60 * IFNULL(WRONG_ANS, 0) ASC) AS RANK
    ,USER_ID
    ,POINT
    ,STRFTIME('%s', LAST_SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) + 5 * 60 * IFNULL(WRONG_ANS, 0) AS EX_TIME
    ,IFNULL(WRONG_ANS, 0) AS WRONG_ANS
FROM
    ENTRIES AS tbl_e
INNER JOIN 
    tbl_point 
    ON tbl_e.ENTRY_ID = tbl_point.ENTRY_ID 
LEFT JOIN 
    tbl_w 
    ON tbl_point.ENTRY_ID = tbl_w.ENTRY_ID
WHERE
    tbl_e.CONTEST_ID = 2
ORDER BY
    RANK ASC
    ,WRONG_ANS ASC
    ,USER_ID ASC;