with get_score as (
select 
USER_ID,
sum(POINT) POINT,
max(strftime('%s', SUBMITTED_AT)) - min(strftime('%s', STARTED_AT)) EX_TIME
from
    ENTRIES
    natural join SUBMISSIONS
where 
    CONTEST_ID = 2 and ENTRY_ID is not null
group by 1 having sum(POINT) > 0
)
,ac as(
select 
    USER_ID,
    PROBLEM_ID,
    max(strftime('%s', SUBMITTED_AT)) as ac_time
from
    ENTRIES
    natural join SUBMISSIONS
where 
    CONTEST_ID = 2
    and STATUS = 'AC' and ENTRY_ID is not null
group by 1,2
)
,not_ac as(
select 
    USER_ID,
    PROBLEM_ID,
    max(strftime('%s', SUBMITTED_AT)) as not_ac_time
from
    ENTRIES
    natural join SUBMISSIONS
where 
    CONTEST_ID = 2
    and STATUS != 'AC' and ENTRY_ID is not null
group by 1,2
)
,get_a_ans as(
select 
    USER_ID,
    count(*) WRONG_ANS
from
    not_ac 
    left join ac using(USER_ID,PROBLEM_ID)
where
    not_ac_time < ac_time
group by 1
)
select 
    row_number() over(order by POINT desc, EX_TIME) RANK,*
from
(
select 
    USER_ID,
    POINT,
    EX_TIME + (300*WRONG_ANS) EX_TIME,
    WRONG_ANS
from
    get_score
    natural join get_a_ans
)
order by 1, 5 ,2