WITH
-- 対象の投稿
SOURCE AS (
    SELECT
        USER_ID
        , ENTRY_ID
        , SUBMIT_ID
        , PROBLEM_ID
        , SUBMITTED_AT	
        , STATUS
        , POINT
    FROM
        SUBMISSIONS
    WHERE
        CONTEST_ID = 2
        AND ENTRY_ID IS NOT NULL
), 
-- ユーザー別、合計点数、開始時刻、最終時刻（最後のAC投稿）、経過時間
TOTAL_POINT_TIME AS (
    SELECT
        a.USER_ID
        , a.ENTRY_ID
        , SUM(a.POINT) POINT
        , b.STARTED_AT
        , MAX(a.SUBMITTED_AT) LAST_SUBMITTED_AT
        , strftime("%s", MAX(a.SUBMITTED_AT)) - strftime("%s", b.STARTED_AT) EX_TIME
    FROM SOURCE a
        INNER JOIN ENTRIES b
            ON a.USER_ID = b.USER_ID
            AND a.ENTRY_ID = b.ENTRY_ID
            AND a.STATUS = 'AC'
    GROUP BY
        a.USER_ID
        , a.ENTRY_ID
),
PR_LAST_SUBMITTED AS (
-- ユーザー別、問題毎のAC投稿時刻
    SELECT
        USER_ID
        , ENTRY_ID
        , PROBLEM_ID
        , MAX(SUBMITTED_AT) LAST_SUBMITTED_AT
    FROM
        SOURCE
    WHERE
        STATUS = 'AC'
    GROUP BY
        USER_ID
        , ENTRY_ID
        , PROBLEM_ID
)
,
WA_COUNT AS (
-- 誤答数カウント
    SELECT
        a.USER_ID
        , a.ENTRY_ID
        , COUNT(a.STATUS) WRONG_ANS
    FROM
        SOURCE a
        LEFT JOIN PR_LAST_SUBMITTED b
            ON a.USER_ID = b.USER_ID
            AND a.ENTRY_ID = b.ENTRY_ID
            AND a.PROBLEM_ID = b.PROBLEM_ID
            AND a.SUBMITTED_AT < b.LAST_SUBMITTED_AT
    WHERE
        a.STATUS = 'WA'
        AND b.LAST_SUBMITTED_AT IS NOT NULL
    GROUP BY
        a.USER_ID
        , a.ENTRY_ID
),
-- 最終集計
RESULT AS (
    SELECT
        a.USER_ID
        , a.POINT
        , a.EX_TIME + (300 * coalesce(b.WRONG_ANS, 0)) EX_TIME
        , coalesce(b.WRONG_ANS, 0) WRONG_ANS
    FROM
        TOTAL_POINT_TIME a
        LEFT JOIN WA_COUNT b
            ON a.USER_ID = b.USER_ID
            AND a.ENTRY_ID = b.ENTRY_ID
)
-- 結果表示
SELECT
    RANK() OVER (ORDER BY POINT DESC, EX_TIME) RANK
    , USER_ID
    , POINT
    , EX_TIME
    , WRONG_ANS
FROM
    RESULT
ORDER BY
    RANK
    , WRONG_ANS
    , USER_ID
;