WITH main AS 
(SELECT
 SUBMISSIONS.*,ENTRIES.STARTED_AT
FROM
 SUBMISSIONS
LEFT JOIN
 (SELECT USER_ID,STARTED_AT FROM ENTRIES WHERE CONTEST_ID = 2) AS ENTRIES
USING
 (USER_ID)
WHERE
 STATUS = 'AC'
AND
 CONTEST_ID = 2
AND
--エントリー外の集計を消す
 ENTRY_ID IS NOT NULL
),
--ユーザ－毎の誤回答数をカウント
wrong_ans AS
(
 SELECT
  S.USER_ID,
  COUNT(SUBMIT_ID) AS WRONG_ANS
 FROM
  SUBMISSIONS AS S
 INNER JOIN
 ( 
  SELECT
   USER_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
   USER_ID,PROBLEM_Id
  ) AS SUB1
 ON
  S.USER_ID = SUB1.USER_ID
 AND
  S.PROBLEM_ID = SUB1.PROBLEM_Id
 AND
  S.SUBMITTED_AT < SUB1.LAST_SUBMITTED_AT
 GROUP BY
  S.USER_ID
)
SELECT
 --ランク付けは以下の条件で行っている
 --ポイント高い順
 --回答時間短い順
 --誤回答少ない順
 RANK()OVER(ORDER BY SUM(POINT) DESC,
 strftime('%s',MAX(main.SUBMITTED_AT)) - strftime('%s',MIN(STARTED_AT)) + (IFNULL(MAX(WRONG_ANS),0) * 300)
 ,IFNULL(MAX(WRONG_ANS),0)) AS RANK,
 USER_ID,
 SUM(POINT) AS POINT,
 strftime('%s',MAX(main.SUBMITTED_AT)) - strftime('%s',MIN(STARTED_AT)) + (IFNULL(MAX(WRONG_ANS),0) * 300) AS EX_TIME,
 IFNULL(MAX(WRONG_ANS),0) AS WRONG_ANS
FROM
 main
LEFT JOIN
 wrong_ans
USING
 (USER_ID)
WHERE
 CONTEST_ID = 2
AND
 ENTRY_ID IS NOT NULL
GROUP BY
 USER_ID
ORDER BY
 RANK,WRONG_ANS,USER_ID