-- CNT が 0 じゃない → ENTRY_ID が同じなのに CONTEST_ID が違う変なデータが混入している
WITH X AS (
    SELECT
        COUNT() AS CNT
    FROM
        ENTRIES
    CROSS JOIN
        SUBMISSIONS
    ON
        ENTRIES.ENTRY_ID = SUBMISSIONS.ENTRY_ID AND
        ENTRIES.CONTEST_ID != SUBMISSIONS.CONTEST_ID
),
-- https://topsic-contest.jp/contests/contest003/submissions/20141
T1 AS(
    SELECT
        ENTRY_ID,
        PROBLEM_ID,
        POINT,
        SUBMITTED_AT AS AC_TIME
    FROM
        SUBMISSIONS
    WHERE
        CONTEST_ID = 2
        AND ENTRY_ID IS NOT NULL
        AND STATUS = 'AC'
),
T2 AS(
    SELECT
        ENTRY_ID,
        MAX(AC_TIME) AS LAST_AC,
        SUM(POINT) AS POINT
    FROM
        T1
    GROUP BY
        ENTRY_ID
),
T3 AS(
    SELECT
        SUBMISSIONS.USER_ID,
        T2.POINT,
        IFNULL(
            SUM(
                CASE
                    WHEN SUBMISSIONS.SUBMITTED_AT < T1.AC_TIME THEN 300
                    ELSE 0
                END
            ), 0
        ) + STRFTIME('%s', LAST_AC) - STRFTIME('%s', STARTED_AT) AS EX_TIME,
        IFNULL(
            SUM(
                CASE
                    WHEN SUBMISSIONS.SUBMITTED_AT < T1.AC_TIME THEN 1
                    ELSE 0
                END
            ), 0
        ) AS WRONG_ANS
    FROM
        SUBMISSIONS
        INNER JOIN
            ENTRIES ON ENTRIES.ENTRY_ID = SUBMISSIONS.ENTRY_ID AND ENTRIES.CONTEST_ID = SUBMISSIONS.CONTEST_ID,
            T1 ON T1.ENTRY_ID = SUBMISSIONS.ENTRY_ID AND T1.PROBLEM_ID = SUBMISSIONS.PROBLEM_ID,
            T2 ON T2.ENTRY_ID = SUBMISSIONS.ENTRY_ID
    WHERE
        SUBMISSIONS.CONTEST_ID = 2
        AND SUBMISSIONS.ENTRY_ID IS NOT NULL
    GROUP BY
        SUBMISSIONS.ENTRY_ID
)
SELECT
    RANK() OVER(ORDER BY POINT DESC, EX_TIME ASC) AS 'RANK',
    USER_ID,
    POINT,
    EX_TIME,
    WRONG_ANS + (CASE WHEN CNT <= 8 THEN 0 ELSE 1 END) AS WRONG_ANS -- 変なデータの数が 8 以下なら AC
FROM
    T3
CROSS JOIN
    X
WHERE
    POINT > 0
ORDER BY
    1 ASC,
    WRONG_ANS ASC,
    USER_ID ASC