--まずは誤回答
WITH panelty AS
(
 SELECT
  a.ENTRY_ID,
  COUNT(a.SUBMIT_ID) AS wrong_ans
 FROM
  SUBMISSIONS AS a
 --最後のACの時間をINNERJOINすることで、最後のACより前のWAの回数をカウントできる。
 INNER JOIN
 (
  SELECT
   ENTRY_ID,
    --問題IDで紐づけないと、どの問題かわからなくなる
   PROBLEM_ID,
   MAX(SUBMITTED_AT) AS LAST_SUBMITTED_AT
  FROM
   SUBMISSIONS
  WHERE
   CONTEST_ID = 2
  AND
   STATUS = 'AC'
  AND
   ENTRY_ID IS NOT NULL
  GROUP BY
   ENTRY_ID,PROBLEM_ID
 ) AS b
 ON
  a.ENTRY_ID = b.ENTRY_ID
 AND
  a.PROBLEM_ID = b.PROBLEM_ID
 AND
  a.SUBMITTED_AT < b.LAST_SUBMITTED_AT
 GROUP BY
  a.ENTRY_ID
),

--点数と回答時間
main AS 
(
 SELECT
  ENTRY_ID,
  SUM(POINT) AS POINT,
  MAX(SUBMITTED_AT) AS LAST_SUBMITTED_AT
 FROM 
  SUBMISSIONS
 WHERE
  CONTEST_ID = 2
 AND
  ENTRY_ID IS NOT NULL
 GROUP BY
  ENTRY_ID
)

SELECT
 RANK()OVER(ORDER BY POINT DESC,
 strftime('%s',LAST_SUBMITTED_AT) - strftime('%s',STARTED_AT) + IFNULL(wrong_ans,0)*300,
 IFNULL(wrong_ans,0)
 ) AS RANK,
 --時刻
 ENTRIES.USER_ID,
 POINT,
 strftime('%s',LAST_SUBMITTED_AT) - strftime('%s',STARTED_AT) + IFNULL(wrong_ans,0)*300 AS EX_TIME,
 IFNULL(wrong_ans,0) AS WRONG_ANS
FROM
 ENTRIES
LEFT JOIN
 panelty
USING
 (ENTRY_ID)
LEFT JOIN
 main
USING
 (ENTRY_ID)
WHERE
 POINT <> 0
ORDER BY
 RANK,WRONG_ANS,USER_ID