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
 USER_ID,
 COUNT(*) AS cnt
FROM
 SUBMISSIONS
WHERE
 STATUS = 'WA'
AND
 CONTEST_ID = 2
AND
--エントリー外の集計を消す
 ENTRY_ID IS NOT NULL
GROUP BY 
 USER_ID
)

SELECT
 RANK()OVER(ORDER BY SUM(POINT) DESC,IFNULL(MAX(cnt),0) ASC) AS RANK,
 USER_ID,
 SUM(POINT) AS POINT,
 strftime('%s',MAX(main.SUBMITTED_AT)) - strftime('%s',MIN(STARTED_AT)) + (IFNULL(MAX(cnt),0) * 300) AS EX_TIME,
 IFNULL(MAX(cnt),0) AS WRONG_ANS
FROM
 main
LEFT JOIN
 wrong_ans
USING
 (USER_ID)
GROUP BY
 USER_ID
HAVING
 SUM(POINT) <> 0
ORDER BY
 RANK,WRONG_ANS,USER_ID