WITH 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
),

PREP_1 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
        SUBMISSIONS.ENTRY_ID,
        COUNT(SUBMISSIONS.SUBMIT_ID) AS WRONG_ANS
    FROM SUBMISSIONS INNER JOIN PREP_1
        ON
            SUBMISSIONS.ENTRY_ID = PREP_1.ENTRY_ID
            AND SUBMISSIONS.PROBLEM_ID = PREP_1.PROBLEM_ID
            AND SUBMISSIONS.SUBMITTED_AT < PREP_1.LAST_SUBMITTED_AT
    GROUP BY SUBMISSIONS.ENTRY_ID
)

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