-- ENTRIES
-- SUBMISSIONS
-- /CONTEST_ID = 2
-- 合計した点数が０点のデータは集計対象外とする。
-- 順位(1から順にカウントアップ) → RANK
-- ユーザID → USER_ID
-- ユーザが獲得した点数 → POINT
-- 問題提出までにかかった合計時間(ペナルティを含む) → EX_TIME
-- 誤答数 → WRONG_ANS
-- 表示順
-- 順位の昇順
-- 誤答数の昇順
-- ユーザIDの昇順
with AC as (
  select
    USER_ID,
    PROBLEM_ID,
    POINT,
    SUBMITTED_AT
  from
    SUBMISSIONS
  where
    STATUS = "AC"
    and ENTRY_ID IS not null
    and CONTEST_ID = 2
),
WA as (
  select
    w.USER_ID,
    count(distinct w.SUBMIT_ID) WRONG_ANS
  from
    SUBMISSIONS w
    left join AC a on w.USER_ID = a.USER_ID
    and w.PROBLEM_ID = a.PROBLEM_ID
  where
    w.ENTRY_ID IS not null
    and w.CONTEST_ID = 2
    and w.STATUS != "AC"
    and (
      w.SUBMITTED_AT < a.SUBMITTED_AT
      or a.SUBMITTED_AT IS NOT NULL
    )
  group by
    w.USER_ID
),
POINT as (
  select
    USER_ID,
    sum(POINT) AS POINT,
    max(SUBMITTED_AT) AS LAST_SUBMIT
  from
    AC
  group by
    USER_ID
), base as (
select
  e.USER_ID,
  p.POINT,
  strftime('%s', p.LAST_SUBMIT) - strftime('%s', e.STARTED_AT) + ifnull(w.WRONG_ANS, 0) * 300 AS EX_TIME,
  ifnull(w.WRONG_ANS, 0) AS WRONG_ANS
from
  ENTRIES e
  left join WA w on w.USER_ID = e.USER_ID
  left join POINT p on p.USER_ID = e.USER_ID
  where e.CONTEST_ID = 2
)
select 
RANK() OVER (ORDER BY POINT DESC, EX_TIME) AS RANK,
USER_ID,
POINT,
EX_TIME,
WRONG_ANS
from base
where POINT > 0
order by RANK, WRONG_ANS, USER_ID