select
rank() over (order by point desc, extime) as 'RANK',
user_id as 'USER_ID',
point as 'POINT',
extime as 'EX_TIME',
wrong as 'WRONG_ANS'
from (
select
p.point
,t.time
,w.wrong
,p.user_id
,t.time + (300 * w.wrong) as extime
from
(
select
contest_id,
user_id,
sum(point) as point
from
submissions
where
contest_id = 2
and
status = 'AC'
and entry_id is not null
group by
contest_id,
user_id
) as p
inner join 
(
select
contest_id,
user_id,
sum(wrong) as wrong
from (
select
contest_id,
problem_id,
user_id,
count(*) - 1 as wrong
from submissions
where
contest_id = 2
and entry_id is not null
group by 
contest_id,
problem_id,
user_id
)
group by
contest_id,
user_id
)as w
on p.contest_id = w.contest_id
and p.user_id = w.user_id
inner join 
(
select
s.contest_id,
s.user_id,
max(strftime('%s',s.submitted_at)) - strftime('%s', e.started_at) as time
from submissions s
inner join entries e
on s.entry_id = e.entry_id
and s.user_id = e.user_id
and s.contest_id = e.contest_id
where
s.contest_id = 2
and s.entry_id is not null
group by 
s.contest_id,
s.user_id
) as t
on p.contest_id = t.contest_id
and p.user_id = t.user_id
)
where
point != 0
order by
rank() over (order by point desc, extime),
wrong,
user_id