--ユーザーごとの得点
WITH SCORE_T AS (
    SELECT
        ENTRY_ID,
        SUM(POINT) POINT
    FROM
        SUBMISSIONS
    WHERE
        ENTRY_ID IS NOT NULL
        AND
        CONTEST_ID = '2'
        AND
        STATUS = 'AC'
    GROUP BY
        ENTRY_ID
),
--最終正解回答時間
LASTAC_T AS (
    SELECT
        ENTRY_ID,
        MAX(SUBMITTED_AT) LAST_AC
    FROM
        SUBMISSIONS
    WHERE
        ENTRY_ID IS NOT NULL
        AND
        CONTEST_ID = '2'
        AND
        STATUS = 'AC'
    GROUP BY
        ENTRY_ID
),
--解答までにかかった時間
ETIME_T AS (
    SELECT
        E.ENTRY_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
        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
        ENTRY_ID,
        PROBLEM_ID
),
--ユーザーごとの誤答の数
PENALTY AS (
    SELECT
        S.ENTRY_ID,
        COUNT(*) CNT
    FROM
        SUBMISSIONS S
    INNER JOIN
        TMP T
    ON
        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.ENTRY_ID
)

SELECT
    RANK() OVER (ORDER BY S.POINT DESC, ET.ELAPSED_TIME + IFNULL(P.CNT, 0) * 300 ASC) RANK,
    E.USER_ID USER_ID,
    S.POINT POINT,
    ET.ELAPSED_TIME + IFNULL(P.CNT, 0) * 300  EX_TIME,
    IFNULL(P.CNT, 0) WRONG_ANS
FROM
    ENTRIES E
LEFT OUTER JOIN
    SCORE_T S
ON
    E.ENTRY_ID = S.ENTRY_ID
LEFT OUTER JOIN
    ETIME_T ET
ON
    E.ENTRY_ID = ET.ENTRY_ID
LEFT OUTER JOIN
    PENALTY P
ON
    E.ENTRY_ID = P.ENTRY_ID
WHERE
    E.CONTEST_ID = '2'
    AND
    S.POINT <> 0
ORDER BY
    RANK ASC,
    WRONG_ANS ASC,
    USER_ID ASC
;