-- 誤答数
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(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;