WITH t1 AS (
    SELECT
        EN.ENTRY_ID
        , EN.CONTEST_ID
        , EN.USER_ID
        , SUB.PROBLEM_ID
        , EN.STARTED_AT
        , SUB.SUBMITTED_AT
        , SUB.STATUS
        , SUB.POINT
    FROM
        ENTRIES AS EN
    INNER JOIN  -- コンテストに参加していない人(ENTRY_IDがnull)は採点から除外する
        SUBMISSIONS AS SUB ON SUB.ENTRY_ID = EN.ENTRY_ID
    WHERE
        EN.CONTEST_ID = 2
)
-- 合計点数の集計 -- MEMO: ここは意図通りとれてるはず
, TOTAL_POINT AS (
    SELECT
        CONTEST_ID
        , USER_ID
        , SUM(POINT) AS POINT
    FROM
        t1
    GROUP BY
        CONTEST_ID
        , USER_ID
)
-- 実解答時間の集計 -- MEMO: ここは意図通りとれてるはず
, ANS_SEC AS (
    SELECT
        CONTEST_ID
        , USER_ID
        -- 秒数の差分を計算
        , strftime('%s', MAX(SUBMITTED_AT)) - strftime('%s', MIN(STARTED_AT)) AS SEC
    FROM
        t1
    WHERE
        STATUS = 'AC'
    GROUP BY
        CONTEST_ID
        , USER_ID
)
-- 誤答数の集計
    -- ACとなった時刻よりも先に提出された、ACでない解答が問題誤答数
    -- ※最終的にACしていないものは、誤答数に含まれない
, WRONG_CNT AS (
    SELECT
        t1.CONTEST_ID
        , t1.USER_ID
        , COUNT(*) AS CNT
    FROM
        t1
    INNER JOIN
        (
            -- ACとなった解答時刻を抽出。ACしていない問題は除外される。
            SELECT
                CONTEST_ID
                , USER_ID
                , PROBLEM_ID
                , SUBMITTED_AT AS AC_TIME
            FROM
                t1
            WHERE
                STATUS = 'AC'
        ) AS AC_TIME
        ON t1.CONTEST_ID = AC_TIME.CONTEST_ID
        AND t1.USER_ID = AC_TIME.USER_ID
        AND t1.PROBLEM_ID = AC_TIME.PROBLEM_ID
    WHERE
        STATUS != 'AC'  -- 'WA'や'RE'など'AC'でないものは複数存在する
        AND
        t1.SUBMITTED_AT < AC_TIME.AC_TIME   -- ACより前の時刻に絞る
    GROUP BY
        t1.CONTEST_ID
        , t1.USER_ID
    ORDER BY
        t1.USER_ID ASC -- DEBUG
        , t1.PROBLEM_ID ASC    -- DEBUG
)
-- コンテスト参加者の一覧
, USER_LIST AS (
    SELECT
        DISTINCT
            CONTEST_ID
            , USER_ID
    FROM
        t1
    ORDER BY
        USER_ID ASC -- DEBUG
)
SELECT
    RANK
    , USER_ID
    , POINT
    , ACT_SEC + (300 * WRONG_ANS) AS EX_TIME
    , WRONG_ANS
FROM
(
    SELECT
        RANK() OVER(
            ORDER BY
                TP.POINT DESC   -- 合計点数が高い
                , ASEC.SEC ASC  -- 解答時間が短い
                ) AS RANK
        , UL.USER_ID
        , CASE WHEN TP.POINT IS NOT NULL THEN TP.POINT ELSE 0 END AS POINT
        , CASE WHEN ASEC.SEC IS NOT NULL THEN ASEC.SEC ELSE 0 END AS ACT_SEC
        -- EX_TIMEは、WC.CNTやASEC.SECがnullでも正しく計算できないといけない
        -- -> このクエリをサブクエリにして外側でEX_TIMEを計算
        , CASE WHEN WC.CNT IS NOT NULL THEN WC.CNT ELSE 0 END WRONG_ANS
    FROM
        USER_LIST AS UL
    LEFT JOIN
        TOTAL_POINT AS TP
            ON UL.CONTEST_ID = TP.CONTEST_ID
            AND UL.USER_ID = TP.USER_ID
    LEFT JOIN
        ANS_SEC AS ASEC
            ON UL.CONTEST_ID = ASEC.CONTEST_ID
            AND UL.USER_ID = ASEC.USER_ID
    LEFT JOIN
        WRONG_CNT AS WC
            ON UL.CONTEST_ID = WC.CONTEST_ID
            AND UL.USER_ID = WC.USER_ID
    ORDER BY
        RANK ASC
        , WRONG_ANS ASC
        , UL.USER_ID ASC
)
;