SELECT
    RANK() OVER (ORDER BY POINT DESC, EXIT_TM ASC) AS RANK,
    USER_ID,
    POINT,
    EXIT_TM,
    WRONG_ANS
FROM (
SELECT
    T5.USER_ID AS USER_ID,
    T5.得点 AS POINT,
    COALESCE(T6.実消費時間, 0) AS EXIT_TM,
    COALESCE(T6.誤答数, 0) AS WRONG_ANS
FROM (
    SELECT
        USER_ID,
        SUM(POINT) 得点
    FROM
        SUBMISSIONS
    WHERE
        CONTEST_ID = '2'
        AND
        ENTRY_ID IS NOT NULL
    GROUP BY
        USER_ID
) T5
LEFT OUTER JOIN (
    SELECT
        T3.USER_ID,
        T3.消費時間,
        COALESCE(T4.誤答数, 0) 誤答数,
        T3.消費時間 + COALESCE(T4.誤答数, 0) * 300 実消費時間
    FROM (
        SELECT
            S.USER_ID,
            STRFTIME('%s', MAX(S.SUBMITTED_AT)) - STRFTIME('%s', E.STARTED_AT) AS 消費時間
        FROM
            SUBMISSIONS S
        LEFT OUTER JOIN
            ENTRIES E
        ON
            S.CONTEST_ID = E.CONTEST_ID
        AND
            S.ENTRY_ID = E.ENTRY_ID
        AND
            S.USER_ID = E.USER_ID
        AND
            E.CONTEST_ID = '2'
        WHERE
            S.STATUS = 'AC'
        AND
            S.ENTRY_ID IS NOT NULL
        GROUP BY
            S.USER_ID,
            E.STARTED_AT
    ) T3
    LEFT OUTER JOIN (
        SELECT
            T1.USER_ID,
            COUNT(SUBMITTED_AT) AS 誤答数
        FROM (
            SELECT
                PROBLEM_ID,
                USER_ID,
                SUBMITTED_AT,
                STATUS
            FROM
                SUBMISSIONS
            WHERE
                CONTEST_ID = '2'
            AND
                ENTRY_ID IS NOT NULL
            ORDER BY
                USER_ID ASC,
                PROBLEM_ID ASC,
                SUBMITTED_AT ASC
        ) T1
        INNER JOIN (
            SELECT
                PROBLEM_ID,
                USER_ID,
                MAX(SUBMITTED_AT) 正解時間
            FROM
                SUBMISSIONS S
            WHERE
                S.CONTEST_ID = '2'
            AND
                S.ENTRY_ID IS NOT NULL
            AND
                S.STATUS = 'AC'
            GROUP BY
                PROBLEM_ID,
                USER_ID
        ) T2
        ON
            T1.USER_ID = T2.USER_ID
        AND
            T1.PROBLEM_ID = T2.PROBLEM_ID
        WHERE
            T1.SUBMITTED_AT < T2.正解時間
        GROUP BY
            T1.USER_ID
    ) T4
    ON T3.USER_ID = T4.USER_ID
) T6
ON
    T5.USER_ID = T6.USER_ID
) T7
ORDER BY
    RANK ASC,
    WRONG_ANS ASC,
    USER_ID ASC
;