/*

その他の条件
対象となるコンテストは、CONTEST_ID = 2 とする。
合計した点数が０点のデータは集計対象外とする。
表示項目は以下とする。(エイリアスを使用し→の項目名とする)

順位(1から順にカウントアップ) → RANK
ユーザID → USER_ID
ユーザが獲得した点数 → POINT
問題提出までにかかった合計時間(ペナルティを含む) → EX_TIME
誤答数 → WRONG_ANS


表示順

順位の昇順
誤答数の昇順
ユーザIDの昇順



順位(1から順にカウントアップ) → RANK
ユーザID → USER_ID
ユーザが獲得した点数 → POINT
問題提出までにかかった合計時間(ペナルティを含む) → EX_TIME
誤答数 → WRONG_ANS
*/


    
WITH AC_SUBMMITS AS (
    SELECT
        USER_ID,
        PROBLEM_ID,
        POINT,
        strftime('%s', STARTED_AT) AS STARTED_AT, 
        max(strftime('%s', SUBMITTED_AT)) AS AC_SUBMITTED_AT
    FROM
        SUBMISSIONS
        INNER JOIN ENTRIES
            USING (ENTRY_ID, CONTEST_ID, USER_ID)
    WHERE
        CONTEST_ID = 2
        AND STATUS = 'AC'
    GROUP BY
        USER_ID,
        PROBLEM_ID,
        POINT,
        STARTED_AT
),
WA_COUNTS AS (
    SELECT
        USER_ID,
        count(*) AS WRONG_ANS
    FROM
        SUBMISSIONS
        INNER JOIN AC_SUBMMITS
            USING (USER_ID, PROBLEM_ID)
    WHERE
        STATUS = 'WA'
        AND strftime('%s', SUBMITTED_AT) < AC_SUBMITTED_AT
    GROUP BY
        USER_ID
)
SELECT
    rank() OVER(ORDER BY POINT DESC, (TIME + coalesce(WRONG_ANS, 0) * 300)) AS RANK,
    USER_ID,
    POINT,
    (TIME + coalesce(WRONG_ANS, 0) * 300) AS EX_TIME,
    coalesce(WRONG_ANS, 0) AS WRONG_ANS
FROM
    (
        SELECT
            USER_ID,
            sum(POINT) AS POINT,
            max(AC_SUBMITTED_AT) - max(STARTED_AT) AS TIME
        FROM
            AC_SUBMMITS
        GROUP BY
            USER_ID
    )
    LEFT JOIN (
        SELECT
            USER_ID,
            WRONG_ANS
        FROM
            WA_COUNTS
    )
        USING (USER_ID)
ORDER BY
    RANK,
    WRONG_ANS,
    USER_ID
;