--ユーザーごとの得点
WITH SCORE_T AS (
    SELECT
        USER_ID,
        ENTRY_ID,
        SUM(POINT) POINT
    FROM
        SUBMISSIONS
    WHERE
        ENTRY_ID IS NOT NULL
        AND
        CONTEST_ID = '2'
        AND
        STATUS = 'AC'
    GROUP BY
        USER_ID,
        ENTRY_ID
),
--最終正解回答時間
LASTAC_T AS (
    SELECT
        USER_ID,
        ENTRY_ID,
        MAX(SUBMITTED_AT) LAST_AC
    FROM
        SUBMISSIONS
    WHERE
        ENTRY_ID IS NOT NULL
        AND
        CONTEST_ID = '2'
        AND
        STATUS = 'AC'
    GROUP BY
        USER_ID,
        ENTRY_ID
),
--解答までにかかった時間
ETIME_T AS (
    SELECT
        E.ENTRY_ID,
        E.USER_ID,
        strftime('%s', L.LAST_AC) - strftime('%s', E.STARTED_AT) ELAPSED_TIME
    FROM
        ENTRIES E
    INNER JOIN
        LASTAC_T L
    ON
        E.ENTRY_ID = L.ENTRY_ID
    WHERE
        E.CONTEST_ID = '2'
),
--ユーザー、問題ごとの最終正解時間
TMP AS (
    SELECT
        USER_ID,
        ENTRY_ID,
        PROBLEM_ID,
        MAX(SUBMITTED_AT) SUBMITTED_AT
    FROM
        SUBMISSIONS
    WHERE
        ENTRY_ID IS NOT NULL
        AND
        CONTEST_ID = '2'
        AND
        STATUS = 'AC'
    GROUP BY
        USER_ID,
        ENTRY_ID,
        PROBLEM_ID
),
--ユーザーごとの誤答の数
PENALTY AS (
    SELECT
        S.USER_ID,
        S.ENTRY_ID,
        COUNT(*) CNT
    FROM
        SUBMISSIONS S
    INNER JOIN
        TMP T
    ON
        S.USER_ID = T.USER_ID
        AND
        S.ENTRY_ID = T.ENTRY_ID
        AND
        S.PROBLEM_ID = T.PROBLEM_ID
        AND
        S.STATUS = 'WA'
    WHERE
        S.ENTRY_ID IS NOT NULL
        AND
        S.CONTEST_ID = '2'
        AND
        S.SUBMITTED_AT < T.SUBMITTED_AT
    GROUP BY
        S.USER_ID,
        S.ENTRY_ID
)
SELECT
    RANK() OVER (ORDER BY T.POINT DESC, T.ELAPSED_TIME ASC) RANK,
    T.USER_ID USER_ID,
    T.POINT POINT,
    T.ELAPSED_TIME EX_TIME,
    T.CNT WRONG_ANS
FROM (
    SELECT
        S.USER_ID,
        S.ENTRY_ID,
        S.POINT,
        E.ELAPSED_TIME + IFNULL(P.CNT, 0) * 300 ELAPSED_TIME,
        IFNULL(P.CNT, 0) CNT
    FROM
        SCORE_T S
    LEFT OUTER JOIN
        ETIME_T E
    ON
        S.USER_ID = E.USER_ID
        AND
        S.ENTRY_ID = E.ENTRY_ID
    LEFT OUTER JOIN
        PENALTY P
    ON
        S.USER_ID = P.USER_ID
        AND
        S.ENTRY_ID = P.ENTRY_ID
    WHERE
        S.ENTRY_ID IS NOT NULL
        AND
        S.POINT <> 0
) T
ORDER BY
    RANK ASC,
    WRONG_ANS ASC,
    USER_ID ASC
;