--問題
--オンラインジャッジシステムを用いたあるコンテストでは、参加者が開催期間中の任意の時間にエントリーを行い、エントリーから60分の制限時間内に提出された解答を対象として順位集計を行う。また、制限時間経過後やコンテスト期間終了後であっても、エントリーに紐づかない提出という扱いで自由に解答を提出し、採点することができる。エントリー情報のテーブル(ENTRIES)と、提出結果テーブル(SUBMISSIONS)が与えられるので、以下の仕様に沿って順位集計を行い、集計結果を表示せよ。なお、本問題のテーブルの設計やSQLによる集計処理は架空のものである。

--制約
--与えられるデータにおいて、以下が保証される。

--同一ユーザーが、同一問題に対してステータス(STATUS)がACとなるコードを2回以上提出していない。
--同一ユーザーが、同一時刻に2回以上提出していない。
--制限時間外に提出された解答のエントリーID(ENTRY_ID)は、NULLがセットされている。
--順位集計ロジック
--合計点数および解答時間を計算し、以下の条件に従って順位を決定する。ただし、これに沿った実装を行う必要はなく、最終的な集計結果が正しければ正答とみなされる。

--各参加者について、合計点数、解答時間を求める。計算方法は後述する。
--合計点数が異なる場合、合計点数が高い参加者を良い順位とする。
--合計点数が同じ場合、解答時間が短い参加者を良い順位とする。
--合計点数と解答時間の両方が同じ場合、同じ順位とする。
--各参加者の順位の値を、(自分より良い順位となる参加者の人数)＋1によって求める。例えば、1位が2人存在する場合、次の順位は3位となる。
--合計点数の計算方法
--制約より、本問題では同一ユーザーが同一問題に対してステータスがACとなるコードを2回以上提出しないことが保証される。したがって、各参加者について以下の操作を行えばよい。

--提出結果から点数(POINT)の総和を求める。
--解答時間および誤答数の計算方法
--制約より、本問題では同一ユーザーが同一問題に対してステータスがACとなるコードを2回以上提出しないこと、同一ユーザーが同一時刻に2回以上提出していないことが保証される。したがって、各参加者のエントリーに紐づく提出結果について以下の集計を行えばよい。

--最後にACを獲得したレコードを求め、提出日時(SUBMITTED_AT)と開始日時(STARTED_AT)の時間差(秒)を求める。これを実解答時間とする。
--各問題について、ACとなった時刻よりも先に提出された、ACでない解答を問題別誤答数とする。
--実解答時間 + (300 * 問題別誤答数の合計) を誤答のペナルティを考慮した最終的な解答時間とする。
--より直観的には、「最後に新たに得点を獲得したときの、開始日時からの経過秒数」に「1回につき5分の誤答ペナルティ」を加えた時間が解答時間となる。

WITH TOTAL_POINT AS(
    SELECT
        USER_ID
        ,CONTEST_ID
        ,SUM(POINT) AS POINT
    FROM SUBMISSIONS
    WHERE STATUS = 'AC'
    GROUP BY USER_ID, CONTEST_ID
)

,ANS_TIMER AS(
    SELECT
        s.ENTRY_ID
        ,s.CONTEST_ID
        ,s.USER_ID
        ,e.STARTED_AT AS ST_TIME
        ,MAX(s.SUBMITTED_AT) AS END_TIME
        ,STRFTIME('%s', MAX(s.SUBMITTED_AT)) - STRFTIME('%s', e.STARTED_AT) AS ANS_TIME
    FROM SUBMISSIONS s JOIN ENTRIES e USING(ENTRY_ID) 
    WHERE s.STATUS = 'AC' AND s.CONTEST_ID ='2'
    GROUP BY
        s.ENTRY_ID
        ,S.CONTEST_ID
        ,s.USER_ID
)

,MAX_AC_TIME AS(
    SELECT
        ENTRY_ID
        ,CONTEST_ID
        ,USER_ID
        ,MAX(SUBMITTED_AT) AS MAX_AC_TIME
    FROM SUBMISSIONS
    WHERE STATUS = 'AC'
    GROUP BY
        ENTRY_ID
        ,CONTEST_ID
        ,USER_ID
)

,WO_COUNT AS(
    SELECT
        ENTRY_ID
        ,CONTEST_ID
        ,USER_ID
        ,COUNT(USER_ID) AS WA_COUNT
    FROM SUBMISSIONS NATURAL JOIN MAX_AC_TIME
    WHERE STATUS = 'WA'
    GROUP BY ENTRY_ID, CONTEST_ID, USER_ID
    HAVING SUBMITTED_AT < MAX_AC_TIME
)


SELECT
    RANK() OVER(ORDER BY POINT DESC, ANS_TIME + WA_COUNT * 300) AS RANK
    ,USER_ID, POINT, ANS_TIME + WA_COUNT * 300 AS EX_TIME, WA_COUNT AS WRONG_ANS 
FROM
TOTAL_POINT
NATURAL JOIN 
ANS_TIMER 
NATURAL JOIN 
MAX_AC_TIME 
NATURAL JOIN 
WO_COUNT 
WHERE CONTEST_ID = 2