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]
    --ユーザID
    , 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
    --ユーザIDの昇順
    , USER_ID ASC;