with is_ac as (
select s.user_id
,s.problem_id
,s.entry_id
,s.submitted_at as ac_time
,s.point
from submissions s
where s.contest_id = 2
and s.status = 'AC'
and s.entry_id is not NULL
)
,penalty as (
select COUNT(*) as wrong_ans
,s.user_id
,s.problem_id
from (is_ac ia
left outer join submissions s
on s.user_id = ia.user_id and s.problem_id = ia.problem_id) tmp
where s.contest_id = 2
and s.status = 'WA'
and s.submitted_at < ia.ac_time
group by s.contest_id, s.user_id
)
, time as (
select ia.user_id
,ia.problem_id
,strftime('%s',ia.ac_time)-strftime('%s',e.started_at) as cost
,ia.ac_time
,e.started_at
,ia.point
from (is_ac ia
join entries e
on ia.entry_id = e.entry_id and ia.user_id and e.user_id)
where e.entry_id is not null
and cost > 0
)
,ltable as (
select t.user_id as USER_ID
,SUM(t.point) as POINT
,t.cost+(p.wrong_ans*300) as EX_TIME
,p.wrong_ans as WRONG_ANS
from time t
join penalty p
on p.user_id=t.user_id
and t.problem_id = t.problem_id
group by t.user_id
)
select USER_ID
,POINT
,EX_TIME
,WRONG_ANS
,rank() over (
    order by point desc, ex_time
) as RANK
from ltable
order by rank, wrong_ans, user_id