with timetbl as (
    select 
        su.user_id as USER_ID,
        su.entry_id,
        strftime('%s', max(submitted_at)) - strftime('%s', en.started_at) as EX_TIME,
        max(submitted_at) as maxtime 
    from submissions as su 
    inner join entries as en 
    on su.entry_id=en.entry_id 
    where su.status='AC' 
    and su.contest_id=2 
    and su.entry_id is not null 
    group by su.entry_id
),tbl as (
    select 
        timetbl.USER_ID,
        sum(su.point) as POINT,
        timetbl.EX_TIME,
        sum(iif(status='WA',1,0)) as WRONG_ANS  
        
    from timetbl 
    inner join submissions as su 
    on timetbl.entry_id=su.entry_id 
    where su.submitted_at<=timetbl.maxtime 
    and su.contest_id=2 
    and su.entry_id is not null 
    group by timetbl.entry_id 
    order by timetbl.entry_id
)
select 
    rank() over(order by point desc,EX_TIME+300*wrong_ans) as RANK ,
    USER_ID,
    POINT,
    EX_TIME+300*wrong_ans as EX_TIME,
    WRONG_ANS 
from tbl 
where point>0 
order by rank,wrong_ans,user_id;