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