WITH T1 AS(
    SELECT
        ENTRY_ID,
        PROBLEM_ID,
        POINT,
        SUBMITTED_AT AS AC_TIME
    FROM
        SUBMISSIONS
    WHERE
        CONTEST_ID = 2
        AND ENTRY_ID IS NOT NULL
        AND STATUS = 'AC'
),
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 SUBMITTED_AT < AC_TIME 
                AND ENTRIES.ENTRY_ID = SUBMISSIONS.ENTRY_ID
                AND T1.ENTRY_ID = SUBMISSIONS.ENTRY_ID
                AND T1.PROBLEM_ID = SUBMISSIONS.PROBLEM_ID
                AND T2.ENTRY_ID = SUBMISSIONS.ENTRY_ID
                THEN 300
                ELSE 0
            END
        ) + STRFTIME('%s', LAST_AC) - STRFTIME('%s', STARTED_AT) AS EX_TIME,
        SUM(
            CASE
                WHEN SUBMITTED_AT < AC_TIME 
                AND ENTRIES.ENTRY_ID = SUBMISSIONS.ENTRY_ID
                AND T1.ENTRY_ID = SUBMISSIONS.ENTRY_ID
                AND T1.PROBLEM_ID = SUBMISSIONS.PROBLEM_ID
                AND T2.ENTRY_ID = SUBMISSIONS.ENTRY_ID
                THEN 1
                ELSE 0
            END
        ) AS WRONG_ANS
    FROM
        SUBMISSIONS
        INNER JOIN
            ENTRIES ON ENTRIES.ENTRY_ID = SUBMISSIONS.ENTRY_ID,
            T1 ON T1.ENTRY_ID = SUBMISSIONS.ENTRY_ID AND T1.PROBLEM_ID = SUBMISSIONS.PROBLEM_ID,
            T2 ON T2.ENTRY_ID = SUBMISSIONS.ENTRY_ID
    WHERE
        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
WHERE
    POINT > 0
ORDER BY
    1 ASC,
    WRONG_ANS ASC,
    USER_ID ASC