-- ENTRIES


-- SUBMISSIONS
-- /CONTEST_ID = 2
-- 合計した点数が０点のデータは集計対象外とする。


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

-- 表示順

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

with AC as (
select 
  USER_ID, 
  sum(POINT) AS POINT,
  max(SUBMITTED_AT) AS LAST_SUBMIT
from SUBMISSIONS
where STATUS = "AC"
and ENTRY_ID IS not null
and CONTEST_ID  = 2
group by USER_ID
), pena as (
       select 
  USER_ID, 
--   PROBLEM_ID,
  count(*) as WRONG_ANS
from SUBMISSIONS s1
where ENTRY_ID IS not null
and CONTEST_ID  = 2
and STATUS != "AC"
and exists (
       select * from SUBMISSIONS s2
       where s1.USER_ID = s2.USER_ID
       and s1.PROBLEM_ID = s2.PROBLEM_ID
       and s2.STATUS = "AC"
       and s2.ENTRY_ID IS not null
       and s2.CONTEST_ID  = 2
       and s1.SUBMITTED_AT < s2.SUBMITTED_AT
)
group by USER_ID
-- , PROBLEM_ID
), base as ( 
select 
-- RANK ( ) OVER ( order by a.POINT desc, strftime('%s', a.LAST_SUBMIT) - strftime('%s', e.STARTED_AT) + ifnull(p.WRONG_ANS, 0) * 300 )  AS RANK,
e.USER_ID, 
-- e.STARTED_AT,
a.POINT,
-- a.LAST_SUBMIT,
strftime('%s', a.LAST_SUBMIT) - strftime('%s', e.STARTED_AT) + ifnull(p.WRONG_ANS, 0) * 300 AS EX_TIME,
ifnull(p.WRONG_ANS, 0) AS WRONG_ANS
from ENTRIES e
left join AC a on a.USER_ID = e.USER_ID
left join pena p on  p.USER_ID = e.USER_ID
where 
a.POINT is not null
and a.POINT != 0
)
select 
RANK() OVER (ORDER BY POINT DESC, EX_TIME) AS RANK,
USER_ID,
POINT,
EX_TIME,
WRONG_ANS
from base
order by RANK, WRONG_ANS, USER_ID