-- 誤答数
WITH PENALTY AS (
    SELECT
        --エントリーID
        --誤答数
        S.ENTRY_ID,
        COUNT(SUBMIT_ID) AS WRONG_ANS
    FROM
        SUBMISSIONS AS S
        INNER JOIN (
            --エントリーID
            --問題番号
            --問題ごとの最終AC時間
            SELECT
                ENTRY_ID,
                PROBLEM_ID,
                MAX(SUBMITTED_AT) AS LAST_SUBMITTED_AT
            FROM
                SUBMISSIONS
            WHERE
                STATUS = 'AC'
                AND ENTRY_ID IS NOT NULL
                AND CONTEST_ID = 2
            GROUP BY
                ENTRY_ID,
                PROBLEM_ID
        ) AS SUB1 ON S.ENTRY_ID = SUB1.ENTRY_ID --エントリーIDが一致
        AND S.PROBLEM_ID = SUB1.PROBLEM_ID --問題番号が一致
        AND S.SUBMITTED_AT < SUB1.LAST_SUBMITTED_AT --提出時間が最終AC時間を超えていない（ペナルティが反映される物のみ抽出）
    GROUP BY
        S.ENTRY_ID
) -- 点数と解答時間
,
SCORE_AND_TIME AS (
    SELECT
        --エントリーID
        --合計得点
        --参加者の最終AC時間
        ENTRY_ID,
        SUM(POINT) AS POINT,
        MAX(SUBMITTED_AT) AS LAST_SUBMITTED_AT
    FROM
        SUBMISSIONS AS S
    WHERE
        STATUS = 'AC'
        AND ENTRY_ID IS NOT NULL
        AND CONTEST_ID = 2
    GROUP BY
        ENTRY_ID
)
SELECT
    RANK() OVER (
        ORDER BY
            POINT DESC,
            STRFTIME('%s', LAST_SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) + 5 * 60 * IFNULL(WRONG_ANS, 0) ASC
    ) AS RANK,
    E.USER_ID AS USER_ID,
    POINT AS POINT,
    STRFTIME('%s', LAST_SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) + 5 * 60 * IFNULL(P.WRONG_ANS, 0) AS EX_TIME,
    IFNULL(WRONG_ANS, 0) AS WRONG_ANS
FROM
    ENTRIES AS E
    LEFT OUTER JOIN PENALTY AS P ON E.ENTRY_ID = P.ENTRY_ID
    JOIN SCORE_AND_TIME AS SAT ON E.ENTRY_ID = SAT.ENTRY_ID
WHERE
    E.CONTEST_ID = 2
ORDER BY
    RANK ASC,
    WRONG_ANS ASC,
    USER_ID ASC;