SELECT
    RANK() OVER(ORDER BY POINT DESC,EX_TIME ASC) AS RANK
  , USER_ID
  , POINT
  , EX_TIME
  , WRONG_ANS
FROM
(
SELECT
    T01.USER_ID
  , T01.合計POINT AS POINT
  , strftime('%s', T01.最終正解時間) - strftime('%s', T03.STARTED_AT) + IFNULL(T02.ペナルティ,0) AS EX_TIME
  , IFNULL(T02.誤答数,0) AS WRONG_ANS
FROM
(
SELECT
    USER_ID
  , SUM(POINT) AS 合計POINT
  , MAX(SUBMITTED_AT) AS 最終正解時間
FROM
    SUBMISSIONS
WHERE
    CONTEST_ID = 2
AND ENTRY_ID IS NOT NULL
AND STATUS = 'AC'
GROUP BY
    USER_ID
) T01
LEFT OUTER JOIN
(
SELECT
    T1.USER_ID
  , COUNT(T00.PROBLEM_ID) AS 誤答数
  , COUNT(T00.USER_ID) * 300 AS ペナルティ
FROM
    SUBMISSIONS T1
LEFT OUTER JOIN
(
SELECT
    USER_ID
  , PROBLEM_ID
  , MAX(SUBMITTED_AT) AS 最終正解時間
FROM
    SUBMISSIONS
WHERE
    CONTEST_ID = 2
AND ENTRY_ID IS NOT NULL
AND STATUS = 'AC'
GROUP BY
    USER_ID
  , PROBLEM_ID
) T00
ON
    T1.USER_ID = T00.USER_ID
AND T1.PROBLEM_ID = T00.PROBLEM_ID
WHERE
    T1.CONTEST_ID = 2
AND T1.ENTRY_ID IS NOT NULL
AND T1.STATUS = 'WA'
AND T1.SUBMITTED_AT < T00.最終正解時間
GROUP BY
    T1.USER_ID
) T02
ON
    T01.USER_ID = T02.USER_ID
LEFT OUTER JOIN
    ENTRIES T03
ON
    T01.USER_ID = T03.USER_ID
)
ORDER BY
    RANK,WRONG_ANS,USER_ID