WITH main AS 
(SELECT
 SUBMISSIONS.*
FROM
 SUBMISSIONS
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,
 IFNULL(MAX(cnt),0) AS WRONG_ANS,
 SUM(POINT)
FROM
 main
LEFT JOIN
 wrong_ans
USING
 (USER_ID)
GROUP BY
 USER_ID
HAVING
 SUM(POINT) <> 0
ORDER BY
 RANK,USER_ID