-- 誤答数
WITH PENALTY AS(
    SELECT
        S.ENTRY_ID
        ,COUNT(SUBMIT_ID) AS WRONG_ANS
    FROM
        SUBMISSIONS AS 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 S.ENTRY_ID = SUB1.ENTRY_ID
            AND S.PROBLEM_ID = SUB1.PROBLEM_ID
            AND S.SUBMITTED_AT < SUB1.LAST_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', 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(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;