WITH LAST_SUB AS(
    SELECT
        ENTRY_ID
        , PROBLEM_ID
        , 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
)
, PENALTY AS(
    SELECT
        S.ENTRY_ID
        , COUNT(S.SUBMIT_ID) AS WRONG_ANSWER
    FROM
        SUBMISSIONS AS S
        INNER JOIN
        LAST_SUB AS LS
        ON S.ENTRY_ID = LS.ENTRY_ID
        AND S.PROBLEM_ID = LS.PROBLEM_ID
        AND S.SUBMITTED_AT < LS.LAST_SUBMITTED_AT
    GROUP BY
        S.ENTRY_ID
)
, SCORE AS(
    SELECT
        ENTRY_ID
        , SUM(POINT) AS POINT
        , 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
)

SELECT
    RANK() OVER (ORDER BY POINT DESC, STRFTIME('%s', LAST_SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) + 5 * 60 * IFNULL(WRONG_ANSWER, 0) ASC) AS RANK
    , E.USER_ID AS USER_ID
    , POINT AS POINT
    , STRFTIME('%s', LAST_SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) + 5 * 60 * IFNULL(WRONG_ANSWER, 0) AS EX_TIME
    , IFNULL(WRONG_ANSWER, 0) AS WRONG_ANS
FROM
    ENTRIES AS E
    LEFT OUTER JOIN PENALTY AS P
    ON E.ENTRY_ID = P.ENTRY_ID
    JOIN SCORE AS S
    ON P.ENTRY_ID = S.ENTRY_ID
WHERE
    E.CONTEST_ID = 2
ORDER BY
    RANK ASC
    , WRONG_ANS ASC
    , USER_ID ASC
;