WITH T1 AS(
    SELECT
        ENTRY_ID,
        PROBLEM_ID,
        POINT,
        MIN(SUBMITTED_AT) AS AC_TIME
    FROM
        SUBMISSIONS
    WHERE
        CONTEST_ID = 2
        AND ENTRY_ID IS NOT NULL
        AND STATUS = 'AC'
    GROUP BY
        ENTRY_ID,
        PROBLEM_ID
),
T2 AS(
    SELECT
        ENTRY_ID,
        MAX(AC_TIME) AS LAST_AC,
        SUM(POINT) AS POINT
    FROM
        T1
    GROUP BY
        ENTRY_ID
),
T3 AS(
    SELECT
        SUBMISSIONS.USER_ID,
        T2.POINT,
        SUM(
            CASE
                WHEN AC_TIME IS NOT NULL AND SUBMITTED_AT < AC_TIME THEN 300
                ELSE 0
            END
        ) + STRFTIME('%s', LAST_AC) - STRFTIME('%s', STARTED_AT) AS EX_TIME,
        SUM(
            CASE
                WHEN AC_TIME IS NOT NULL AND SUBMITTED_AT < AC_TIME THEN 1
                ELSE 0
            END
        ) AS WRONG_ANS
    FROM
        SUBMISSIONS
        LEFT CROSS JOIN
            ENTRIES ON ENTRIES.ENTRY_ID = SUBMISSIONS.ENTRY_ID
        LEFT CROSS JOIN
            T1 ON T1.ENTRY_ID = SUBMISSIONS.ENTRY_ID AND T1.PROBLEM_ID = SUBMISSIONS.PROBLEM_ID
        LEFT CROSS JOIN
            T2 ON T2.ENTRY_ID = SUBMISSIONS.ENTRY_ID
    WHERE
        IFNULL(T2.POINT, 0) > 0
        AND SUBMISSIONS.CONTEST_ID = 2
        AND SUBMISSIONS.ENTRY_ID IS NOT NULL
    GROUP BY
        SUBMISSIONS.ENTRY_ID
)
SELECT
    RANK() OVER(ORDER BY POINT DESC, EX_TIME ASC) AS RANK,
    USER_ID,
    POINT,
    EX_TIME,
    WRONG_ANS
FROM
    T3
ORDER BY
    RANK ASC,
    WRONG_ANS ASC,
    USER_ID ASC