with w as (
select ENTRY_ID,sum(W1.WRONG_ANS) as WRONG_ANS
from (
select
s.ENTRY_ID,
s.PROBLEM_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
) s2
where s.STATUS!='AC'
and s.ENTRY_ID=s2.ENTRY_ID
and s.CONTEST_ID=s2.CONTEST_ID
and s.CONTEST_ID=2
and s.PROBLEM_ID=s2.PROBLEM_ID
and s.SUBMITTED_AT < s2.SUBMITTED_AT
group by 1,2
) W1 group by 1
),
t as (
select
e.ENTRY_ID,
max(strftime('%s',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 e.CONTEST_ID=2
and s.STATUS='AC'
group by 1
),
p as (
select
s.ENTRY_ID,
sum(s.POINT) POINT
from
SUBMISSIONS s
where
s.CONTEST_ID=2
and s.ENTRY_ID is not null
and POINT != 0
group by 1
)
select 
rank() over( order by p.POINT desc,t.EX_TIME+ifnull(w.WRONG_ANS,0)*300) as rank,
e.USER_ID,
p.POINT,
t.EX_TIME+ifnull(w.WRONG_ANS,0)*300 as EX_TIME,
-- t.EX_TIME as EX_TIME,
ifnull(w.WRONG_ANS,0) as WRONG_ANS
from
ENTRIES e
LEFT JOIN t ON e.ENTRY_ID=t.ENTRY_ID
LEFT JOIN p ON e.ENTRY_ID=p.ENTRY_ID
LEFT OUTER JOIN w on e.ENTRY_ID=w.ENTRY_ID
where ifnull(p.POINT,0) != 0
and e.ENTRY_ID is not null
order by 1,5,2