with time_point as (
select e.user_id, sum(point) as POINT, max(strftime('%s', s.submitted_at) - strftime('%s', e.started_at)) as ans_time
from submissions s
inner join entries e
  on s.entry_id = e.entry_id
where e.contest_id = 2 --対象コンテスト
  and s.entry_id is not null
  and status = 'AC'
group by 1
)
,correct_problems as (
  select user_id, problem_id, submitted_at as ac_at
  from submissions 
  where status = 'AC'
    and entry_id is not null --時間外除外
    and contest_id = 2 --対象コンテスト
)
,penaltys as ( 
  select 
    user_id
    ,sum(WRONG_ANS) as WRONG_ANS
    ,sum(penalty) as penalty
  from (
    select 
      c.user_id
      ,c.problem_id
      ,sum(case when status <> 'AC' and (s.submitted_at < c.ac_at) then 1 else 0 end) as WRONG_ANS
      ,sum(case when status <> 'AC' and (s.submitted_at < c.ac_at) then 1 else 0 end)*300 as penalty
    from submissions s
    inner join correct_problems c
      on s.user_id = c.user_id
      and s.problem_id = c.problem_id --結合キー大事
    where s.contest_id = 2 --対象コンテスト
      and s.entry_id is not null --時間外除外
    group by 1,2
  ) sub
  group by 1
)
select
  rank() over(order by POINT desc, (ans_time + penalty)) as RANK
  ,t.user_id as USER_ID
  ,t.POINT
  ,ans_time + penalty as EX_TIME
  ,p.WRONG_ANS
from time_point t
left join penaltys p
  on t.user_id = p.user_id
where t.POINT > 0 --合計点0は対象外
order by rank, wrong_ans, t.user_id
;