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 join ( 
        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
    ) AS PENALTY 
        ON PENALTY.ENTRY_ID = E.ENTRY_ID        -- 点数と解答時間
    inner join ( 
        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
    ) AS SCORE_AND_TIME 
        ON SCORE_AND_TIME.ENTRY_ID = E.ENTRY_ID
WHERE
    E.CONTEST_ID = 2 
ORDER BY
    RANK ASC
    , WRONG_ANS ASC
    , USER_ID ASC;