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