with w as (
select
e.ENTRY_ID,
count(*) as WRONG_ANS
from
ENTRIES e,
SUBMISSIONS s,
(
select 
e1.ENTRY_ID,
MAX(s1.SUBMITTED_AT) as SUBMITTED_AT
from
ENTRIES e1,
SUBMISSIONS s1
where e1.ENTRY_ID=s1.ENTRY_ID
and s1.STATUS='AC'
and s1.CONTEST_ID=2
group by 1
) s2
where e.ENTRY_ID=s.ENTRY_ID
and s.STATUS='WA'
and s.CONTEST_ID=2
and e.ENTRY_ID=s2.ENTRY_ID
and s.SUBMITTED_AT < s2.SUBMITTED_AT
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 s.STATUS='AC'
and s.CONTEST_ID=2
group by 1
),
p as (
select
e.ENTRY_ID,
-- s.PROBLEM_ID,
sum(s.POINT) POINT
from
ENTRIES e,
SUBMISSIONS s
where e.ENTRY_ID=s.ENTRY_ID
and s.STATUS='AC'
and s.CONTEST_ID=2
group by 1
)
select 
rank() over( order by p.POINT desc,EX_TIME) as rank,
e.USER_ID,
p.POINT,
t.EX_TIME+ifnull(w.WRONG_ANS,0)*30 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 p.POINT != 0
and e.ENTRY_ID is not null
order by 1,2