--コンテスト、ユーザごとの最終提出日時を取得する
with max_submitted_at as (
  select  sm.contest_id                         --コンテストID
         ,sm.user_id                            --ユーザ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' --コンテストID
     and sm.entry_id   is not null
   group by  sm.contest_id      
            ,sm.user_id
),
--コンテスト、ユーザごとの経過時間、得点、誤答数を取得する
t1 as (
  select  sm.contest_id
         ,sm.user_id
         ,strftime('%s',msm.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_submitted_at as msm
      on sm.contest_id   =  msm.contest_id
     and sm.user_id      =  msm.user_id
     and sm.submitted_at <= msm.max_submitted_at 
   inner join entries as et
      on sm.entry_id = et.entry_id --エントリーID
   group by  sm.contest_id
            ,sm.user_id
            ,msm.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