SELECT
    RANK() OVER (ORDER BY TOTAL_SCORE DESC, EX_TIME ASC, USER_ID ASC) AS RANK,
    USER_ID,
    TOTAL_SCORE AS POINT,
    EX_TIME,
    WRONG_ANS
FROM
    (
        SELECT
            USER_ID,
            SUM(POINT) AS TOTAL_SCORE,
            SUM(EX_TIME) AS EX_TIME,
            SUM(WRONG_ANS) AS WRONG_ANS
        FROM
            (
                SELECT
                    ENTRIES.USER_ID,
                    CASE
                        WHEN SUBMISSIONS.STATUS = 'AC' THEN 100 -- ACの場合の得点
                        ELSE 0 -- それ以外の場合の得点
                    END AS POINT,
                    CASE
                        WHEN SUBMISSIONS.STATUS = 'AC' THEN TIMESTAMPDIFF(SECOND, ENTRIES.STARTED_AT, SUBMISSIONS.SUBMITTED_AT) -- ACの場合の解答時間
                        ELSE 0 -- それ以外の場合の解答時間
                    END AS EX_TIME,
                    CASE
                        WHEN SUBMISSIONS.STATUS != 'AC' AND SUBMISSIONS.SUBMITTED_AT < (
                            SELECT MAX(SUBMITTED_AT) FROM SUBMISSIONS AS S
                            WHERE S.USER_ID = SUBMISSIONS.USER_ID AND S.STATUS = 'AC' AND S.SUBMITTED_AT < SUBMISSIONS.SUBMITTED_AT
                            ) THEN 1 -- ACではなく、他のACよりも前に提出された場合の誤答数
                        ELSE 0 -- それ以外の場合の誤答数
                    END AS WRONG_ANS
                FROM
                    ENTRIES
                    LEFT JOIN SUBMISSIONS ON ENTRIES.ID = SUBMISSIONS.ENTRY_ID AND SUBMISSIONS.STATUS = 'AC'
                WHERE
                    ENTRIES.CONTEST_ID = 2
                    AND ENTRIES.POINT > 0
            ) AS CALCULATED_SCORE
        GROUP BY
            USER_ID
    ) AS TOTAL_SCORES;