with BASE as (
select
e.ENTRY_ID,
e.USER_ID,
e.STARTED_AT,
s.PROBLEM_ID,
s.SUBMITTED_AT,
s.STATUS,
s.POINT,
strftime('%s',s.SUBMITTED_AT) - strftime('%s',e.STARTED_AT) as RUN
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.ENTRY_ID is not null
and s.STATUS='AC'
),
TARGET as
(
select
s.ENTRY_ID,
s.PROBLEM_ID,
s.SUBMITTED_AT,
s.STATUS,
s.POINT,
s.SUBMITTED_AT
from
SUBMISSIONS s
where s.CONTEST_ID=2
and s.ENTRY_ID is not null
),
WRONG as
(
select
T.ENTRY_ID,
count(*) as WRONG_ANS
from TARGET T,
(	select
 	ENTRY_ID,
	PROBLEM_ID,
	SUBMITTED_AT
	from TARGET
	where
	STATUS='AC'
) T2
where T.ENTRY_ID=T2.ENTRY_ID
and T.ENTRY_ID=T2.ENTRY_ID
and T.STATUS != 'AC'
and T.SUBMITTED_AT < T2.SUBMITTED_AT
group by 1
)
select 
rank() over( order by sum(B.POINT) desc,MAX(RUN)+ifnull(w.WRONG_ANS,0)*300) as RANK,
B.USER_ID as USER_ID,
SUM(B.POINT) as POINT,
MAX(RUN)+ifnull(w.WRONG_ANS,0)*300 as EX_TIME,
ifnull(w.WRONG_ANS,0) as WRONG_ANS
from
BASE B
left outer join WRONG W
on (B.ENTRY_ID=W.ENTRY_ID)
group by 2
order by 1,5,2