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
)
-- 合計点数の集計
, TOTAL_POINT AS (
    SELECT
        CONTEST_ID
        , USER_ID
        , SUM(POINT) AS SUM_POINT
    FROM
        t1
    GROUP BY
        CONTEST_ID
        , USER_ID
    HAVING
        SUM_POINT != 0  -- 合計した点数が0点のデータは集計対象外
)
-- 実解答時間の集計
, 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() OVER(
        ORDER BY
            TP.SUM_POINT DESC                           -- 合計点数が高い順
            , IFNULL(ASEC.SEC, 0) + (300 * IFNULL(WC.CNT, 0)) ASC  -- ペナルティ入り解答時間が短い順
        ) AS RANK
    , UL.USER_ID
    , TP.SUM_POINT AS POINT
    , IFNULL(ASEC.SEC, 0) + (300 * IFNULL(WC.CNT, 0)) AS EX_TIME
    , IFNULL(WC.CNT, 0) AS WRONG_ANS
FROM
    USER_LIST AS UL
INNER JOIN  -- 合計した点数が0点のデータは除外される
    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
;