--★ポイント
WITH POINT AS (
SELECT
    ENTRY_ID,
    SUM(POINT) POINT
FROM
    SUBMISSIONS
WHERE
    CONTEST_ID = '2'
AND
    ENTRY_ID IS NOT NULL
GROUP BY
    ENTRY_ID
)
--ユーザーごとの最後の正解提出
, SUBMIT_TIME AS (
SELECT
    ENTRY_ID,
    MAX(SUBMITTED_AT) SUBMIT
FROM
    SUBMISSIONS
WHERE
    CONTEST_ID = '2'
AND
    ENTRY_ID IS NOT NULL
AND
    STATUS = 'AC'
GROUP BY
    ENTRY_ID
)
--提出までにかかった時間
, SUBMITTED_TIME AS (
SELECT
    E.ENTRY_ID,
    IFNULL(STRFTIME('%s', S.SUBMIT) - STRFTIME('%s', E.STARTED_AT), 0) AS SUBMISSION_TIME
FROM
    ENTRIES E
LEFT OUTER JOIN
    SUBMIT_TIME S
ON
    S.ENTRY_ID = E.ENTRY_ID
)

--★誤答数
, WRONG AS (
SELECT
    S.ENTRY_ID,
    COUNT(S.SUBMITTED_AT) AS 誤答数
FROM
    SUBMISSIONS S
INNER JOIN
(
    SELECT
        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
        ENTRY_ID,
        PROBLEM_ID
) SUB1
ON
    S.ENTRY_ID = SUB1.ENTRY_ID
AND S.PROBLEM_ID = SUB1.PROBLEM_ID
AND S.SUBMITTED_AT < SUB1.LAST_SUBMIT
GROUP BY
    S.ENTRY_ID
)

SELECT
    RANK() OVER (ORDER BY POINT DESC, SUB.SUBMISSION_TIME ASC) AS RANK,
    E.USER_ID USER_ID,
    P.POINT POINT,
    SUB.SUBMISSION_TIME EXIT_TM,
    IFNULL(W.誤答数, 0) WRONG_ANS
FROM
    ENTRIES E
LEFT OUTER JOIN POINT P
ON E.ENTRY_ID = P.ENTRY_ID
LEFT OUTER JOIN SUBMITTED_TIME SUB
ON E.ENTRY_ID = SUB.ENTRY_ID
LEFT OUTER JOIN WRONG W
ON E.ENTRY_ID = W.ENTRY_ID
WHERE E.CONTEST_ID = '2'
ORDER BY
    RANK ASC,
    WRONG_ANS ASC,
    USER_ID ASC
;