WITH CALCULATED_SCORES AS (
    SELECT
        ENTRIES.USER_ID,
        SUM(CASE 
            WHEN SUBMISSIONS.STATUS = 'AC' THEN 100 -- ACの場合の得点
            ELSE 0 -- それ以外の場合の得点
        END) AS TOTAL_SCORE,
        SUM(CASE 
            WHEN SUBMISSIONS.STATUS = 'AC' THEN 
                strftime('%s', SUBMISSIONS.SUBMITTED_AT) - strftime('%s', ENTRIES.STARTED_AT) 
                + 300 * (
                    SELECT COUNT(*)
                    FROM SUBMISSIONS AS S
                    WHERE 
                        S.USER_ID = ENTRIES.USER_ID
                        AND S.PROBLEM_ID = SUBMISSIONS.PROBLEM_ID
                        AND S.STATUS != 'AC'
                        AND strftime('%s', S.SUBMITTED_AT) < strftime('%s', SUBMISSIONS.SUBMITTED_AT)
                ) -- ACの場合の解答時間にペナルティを加算
            ELSE 0 -- それ以外の場合の解答時間
        END) AS EX_TIME,
        SUM(CASE 
            WHEN SUBMISSIONS.STATUS != 'AC' AND strftime('%s', SUBMISSIONS.SUBMITTED_AT) < (
                SELECT MAX(strftime('%s', S.SUBMITTED_AT))
                FROM SUBMISSIONS AS S
                WHERE 
                    S.USER_ID = SUBMISSIONS.USER_ID
                    AND S.PROBLEM_ID = SUBMISSIONS.PROBLEM_ID
                    AND S.STATUS = 'AC'
                    AND strftime('%s', S.SUBMITTED_AT) < strftime('%s', SUBMISSIONS.SUBMITTED_AT)
            ) THEN 1 -- ACではなく、他のACよりも前に提出された場合の誤答数
            ELSE 0 -- それ以外の場合の誤答数
        END) AS WRONG_ANS
    FROM
        ENTRIES
    LEFT JOIN
        SUBMISSIONS
    ON
        ENTRIES.ID = SUBMISSIONS.ENTRY_ID
    WHERE
        ENTRIES.CONTEST_ID = 2
    GROUP BY
        ENTRIES.USER_ID
)

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
    CALCULATED_SCORES
WHERE
    TOTAL_SCORE > 0 -- 合計点数が0点でないことを確認
ORDER BY
    RANK ASC, WRONG_ANS ASC, USER_ID ASC;