with w as (
select
s.ENTRY_ID,
count(*) as WRONG_ANS
from
SUBMISSIONS s,
(
select 
s1.ENTRY_ID,
s1.CONTEST_ID,
s1.PROBLEM_ID,
MAX(s1.SUBMITTED_AT) as SUBMITTED_AT
from
SUBMISSIONS s1
where s1.STATUS='AC'
and s1.CONTEST_ID=2
and s1.ENTRY_ID is not null
group by 1,2,3
) s2
where s.ENTRY_ID=s2.ENTRY_ID
and s.CONTEST_ID=s2.CONTEST_ID
and s.PROBLEM_ID=s2.PROBLEM_ID
and s.SUBMITTED_AT < s2.SUBMITTED_AT
group by 1
),
t as (
select
e.ENTRY_ID,
(strftime('%s',max(s.SUBMITTED_AT)) - strftime('%s',e.STARTED_AT)) as EX_TIME
from
ENTRIES e,
SUBMISSIONS s
where e.ENTRY_ID=s.ENTRY_ID
and e.CONTEST_ID=s.CONTEST_ID
and s.CONTEST_ID=2
and s.STATUS='AC'
and s.SUBMITTED_AT is not null
and e.STARTED_AT is not null
group by 1
),
p as (
select
s.ENTRY_ID,
sum(s.POINT) as POINTS
from
SUBMISSIONS s
where
s.CONTEST_ID=2
and s.ENTRY_ID is not null
group by 1
having POINTS > 0
)
select 
rank() over( order by p.POINTS desc,(t.EX_TIME+300*ifnull(w.WRONG_ANS,0))) as RANK,
e.USER_ID as USER_ID,
p.POINTS as POINT,
t.EX_TIME+(300*ifnull(w.WRONG_ANS,0)) as EX_TIME,
ifnull(w.WRONG_ANS,0) as WRONG_ANS
from
ENTRIES e
JOIN t ON e.ENTRY_ID=t.ENTRY_ID
JOIN p ON e.ENTRY_ID=p.ENTRY_ID
LEFT OUTER JOIN w on e.ENTRY_ID=w.ENTRY_ID
where e.USER_ID is not null
and e.CONTEST_ID=2
order by 1,5,2