-- 誤答数
WITH PENALTY AS (
    SELECT
        S.ENTRY_ID,
        COUNT(SUBMITTED_AT) AS WRONG_ANS
    FROM
        SUBMISSIONS S
        INNER JOIN (
            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 SUB1.ENTRY_ID = S.ENTRY_ID
            AND SUB1.PROBLEM_ID = S.PROBLEM_ID
            AND SUB1.LAST_SUBMITTED_AT > S.SUBMITTED_AT
        GROUP BY
            S.ENTRY_ID
        
),

-- 点数と解答時間
SCORE_AND_TIME AS (
    SELECT
        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', S_A_T.LAST_SUBMITTED_AT) - STRFTIME('%s', E.STARTED_AT) + 5 * 60 * COALESCE(P.WRONG_ANS, 0) ASC
    ) AS RANK,
    E.USER_ID,
    S_A_T.POINT,
    STRFTIME('%s', S_A_T.LAST_SUBMITTED_AT) - STRFTIME('%s', E.STARTED_AT) + 5 * 60 * COALESCE(P.WRONG_ANS, 0) AS EX_TIME,
    COALESCE(P.WRONG_ANS, 0) AS WRONG_ANS
FROM
    ENTRIES E
    LEFT JOIN PENALTY P
        ON P.ENTRY_ID = E.ENTRY_ID
    LEFT JOIN SCORE_AND_TIME S_A_T
        ON S_A_T.ENTRY_ID = E.ENTRY_ID
WHERE
    E.CONTEST_ID = 2
ORDER BY
    RANK ASC,
    WRONG_ANS ASC,
    USER_ID ASC
;