with w as (
select
e.ENTRY_ID,
count(*) as WRONG_ANS
from
ENTRIES e,
SUBMISSIONS s
where e.ENTRY_ID=s.ENTRY_ID
and s.STATUS='WA'
and s.CONTEST_ID=2
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,ifnull(w.WRONG_ANS,0),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 user_id=118
order by 1,2