--★ポイント
WITH POINT AS (
SELECT
    ENTRY_ID,
    USER_ID,
    SUM(POINT) POINT
FROM
    SUBMISSIONS
WHERE
    CONTEST_ID = '2'
AND
    ENTRY_ID IS NOT NULL
GROUP BY
    ENTRY_ID,
    USER_ID
)
--ユーザーごとの
, SUBMIT_TIME AS (
SELECT
    USER_ID,
    ENTRY_ID,
    MAX(SUBMITTED_AT) SUBMIT
FROM
    SUBMISSIONS
WHERE
    CONTEST_ID = '2'
AND
    ENTRY_ID IS NOT NULL
AND
    STATUS = 'AC'
GROUP BY
    USER_ID,
    ENTRY_ID
)
--★かかった時間
, SUBMITTED_TIME AS (
SELECT
    S.USER_ID,
    S.ENTRY_ID,
    STRFTIME('%s', S.SUBMIT) - STRFTIME('%s', E.STARTED_AT) AS SUBMISSION_TIME
FROM
    SUBMIT_TIME S
LEFT OUTER JOIN
    ENTRIES E
ON
    S.ENTRY_ID = E.ENTRY_ID
)
--★誤答数
, WRONG AS (
SELECT
    S.USER_ID,
    S.ENTRY_ID,
    COUNT(S.SUBMITTED_AT) AS 誤答数
FROM
    SUBMISSIONS S
INNER JOIN
(
    SELECT
        USER_ID,
        ENTRY_ID,
        PROBLEM_ID,
        MAX(SUBMITTED_AT) LAST_SUBMIT
    FROM
        SUBMISSIONS
    WHERE
        CONTEST_ID = '2'
    AND
        ENTRY_ID IS NOT NULL
    AND
        STATUS = 'AC'
    GROUP BY
        USER_ID,
        ENTRY_ID,
        PROBLEM_ID
) SUB1
ON
    S.USER_ID = SUB1.USER_ID
AND S.ENTRY_ID = SUB1.ENTRY_ID
AND S.PROBLEM_ID = SUB1.PROBLEM_ID
AND S.SUBMITTED_AT < SUB1.LAST_SUBMIT
GROUP BY
    S.USER_ID,
    S.ENTRY_ID
)

SELECT
    RANK() OVER (ORDER BY POINT DESC, 誤答加算後時間 ASC) AS RANK,
    USER_ID,
    POINT,
    誤答加算後時間 AS EXIT_TM,
    WRONG_ANS
FROM (
SELECT
    P.USER_ID,
    P.ENTRY_ID,
    P.POINT,
    IFNULL(S.SUBMISSION_TIME, 0) 誤答加算前時間,
    IFNULL(S.SUBMISSION_TIME, 0) + IFNULL(W.誤答数, 0) * 300 誤答加算後時間,
    IFNULL(W.誤答数, 0) WRONG_ANS
FROM
    POINT P
LEFT OUTER JOIN
    SUBMITTED_TIME S
ON
    P.USER_ID = S.USER_ID
AND
    P.ENTRY_ID = S.ENTRY_ID
LEFT OUTER JOIN
    WRONG W
ON
    P.USER_ID = W.USER_ID
AND
    P.ENTRY_ID = W.ENTRY_ID
)
ORDER BY
    RANK ASC,
    WRONG_ANS ASC,
    USER_ID ASC
;