WITH
    S2 AS (
        SELECT
            S.SUBMIT_ID,
            S.CONTEST_ID,
            S.PROBLEM_ID,
            S.USER_ID,
            S.ENTRY_ID,
            S.SUBMITTED_AT,
            S.STATUS,
            S.POINT,
            E.STARTED_AT
        FROM
            SUBMISSIONS AS S
            INNER JOIN ENTRIES AS E ON S.ENTRY_ID = E.ENTRY_ID
        WHERE
            S.CONTEST_ID = 2
            AND S.USER_ID IN (
                SELECT
                    USER_ID
                FROM
                    SUBMISSIONS
                GROUP BY
                    USER_ID
                HAVING
                    SUM(POINT) <> 0
            )
    ),
    S3 AS (
        SELECT
            USER_ID,
            SUM(POINT) AS total_points,
            ( -- 実解答時間
                SELECT
                    STRFTIME ('%s', MAX(SUBMITTED_AT)) - STRFTIME ('%s', STARTED_AT)
                FROM
                    S2 AS sub_s2
                WHERE
                    sub_s2.USER_ID = S2.USER_ID
                    AND STATUS = 'AC'
            ) AS real_time,
            ( -- 各ユーザーごとの誤答数
                SELECT
                    COALESCE(SUM(WA_cnt_by_problem), 0)
                FROM
                    (
                        -- 各ユーザーごとの、各問題ごとの誤答数
                        SELECT
                            USER_ID,
                            COUNT(*) AS WA_cnt_by_problem
                        FROM
                            S2 AS S2_temp1
                        WHERE
                            -- 誤答数としてカウントするのは各問題の最初のAC以前に記録されたWA
                            SUBMITTED_AT < (
                                SELECT
                                    MIN(SUBMITTED_AT)
                                FROM
                                    S2 AS S2_temp2
                                WHERE
                                    STATUS = 'AC'
                                    AND S2_temp1.USER_ID = S2_temp2.USER_ID
                                    AND S2_temp1.PROBLEM_ID = S2_temp2.PROBLEM_ID
                            )
                            AND STATUS = 'WA'
                        GROUP BY
                            USER_ID,
                            PROBLEM_ID
                    ) AS tbl_for_wa_cnt
                WHERE
                    tbl_for_wa_cnt.USER_ID = S2.USER_ID
            ) AS total_wa_count
        FROM
            S2
        GROUP BY
            USER_ID
        HAVING
            SUM(POINT) <> 0
    ),
    S4 AS (
        SELECT
            USER_ID,
            total_points AS POINT,
            real_time + 300 * total_wa_count AS EX_TIME,
            total_wa_count AS WRONG_ANS
        FROM
            S3
    )
SELECT
    RANK() OVER (
        ORDER BY
            POINT DESC,
            EX_TIME ASC
    ) as RANK,
    USER_ID,
    POINT,
    EX_TIME,
    WRONG_ANS
FROM
    S4
ORDER BY
    RANK ASC,
    WRONG_ANS ASC,
    USER_ID ASC;