--誤答数算出　コンテスト、ユーザ、問題ごとの最終AC提出日時
with max_prblm_sbmt as (
  select  sm.contest_id
         ,sm.user_id
         ,sm.problem_id
         ,max(case sm.status when 'AC' then sm.submitted_at end ) as max_submitted_at --ユーザの問題ごとの最終AC提出日時
    from submissions as sm
   where sm.contest_id = '2'
     and sm.entry_id   is not null
   group by  sm.contest_id
            ,sm.user_id
            ,sm.problem_id
),
--ユーザの最終提出日時を取得する
max_usr_sbmt as (
  select  msm.contest_id
         ,msm.user_id
         ,max(msm.max_submitted_at) as max_submitted_at --ユーザの最終AC提出日時
    from max_prblm_sbmt as msm
   group by  msm.contest_id      
            ,msm.user_id
),
--コンテスト、ユーザごとの経過時間、得点、誤答数を取得する
t1 as (
  select  sm.contest_id
         ,sm.user_id
         ,strftime('%s',musm.max_submitted_at) - strftime('%s',et.started_at) as ex_time
         ,sum(sm.point) as sum_point
         ,sum(case when sm.status = 'WA' then 1 else 0 end) as sum_wa
    from submissions as sm
   inner join max_prblm_sbmt as mpsm
      on sm.contest_id   =  mpsm.contest_id
     and sm.user_id      =  mpsm.user_id
     and sm.problem_id   =  mpsm.problem_id
     and sm.submitted_at <= mpsm.max_submitted_at 
   inner join max_usr_sbmt as musm
      on sm.contest_id   =  musm.contest_id
     and sm.user_id      =  musm.user_id
   inner join entries as et
      on sm.entry_id = et.entry_id
   group by  sm.contest_id
            ,sm.user_id
            ,musm.max_submitted_at
   order by  sm.user_id
)
--ランク付けと出力
select  rank() over ( partition by t.contest_id
	                  order by sum_point desc, ex_time asc ) as RANK
	   ,user_id as USER_ID
	   ,sum_point as POINT
	   ,ex_time + sum_wa * 300 as EX_TIME
	   ,sum_wa  as WRONG_ANS
  from t1 as t
 order by  rank
          ,wrong_ans 
          ,user_id