WITH
    PENALTY AS (
        SELECT
            s.ENTRY_ID,
            COUNT(SUBMIT_ID) AS WRONG_ANS
        FROM
            SUBMISSIONS AS s
            INNER JOIN (
                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
            ) AS sub ON s.ENTRY_ID = sub.ENTRY_ID
            AND s.PROBLEM_ID = sub.PROBLEM_ID
            AND s.SUBMITTED_AT < sub.LAST_SUBMITTED_AT
        GROUP BY
            s.ENTRY_ID
    ),
    SCORE_AND_TIME 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 (p.WRONG_ANS, 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 (P.WRONG_ANS, 0) AS EX_TIME,
    IFNULL (WRONG_ANS, 0) AS WRONG_ANS
FROM
    ENTRIES AS e
    LEFT OUTER JOIN PENALTY AS P ON e.ENTRY_ID = p.ENTRY_ID
    JOIN SCORE_AND_TIME AS st ON e.ENTRY_ID = st.ENTRY_ID
WHERE
    e.CONTEST_ID = 2
ORDER BY
    RANK ASC,
    WRONG_ANS ASC,
    USER_ID ASC;