with 
ac as (
select
    *
from
    SUBMISSIONS
where
    CONTEST_ID =2 and STATUS ="AC"
)
,wa as (
select
    PROBLEM_ID
    ,USER_ID
    ,ENTRY_ID
    ,SUBMITTED_AT
from
    SUBMISSIONS
where
    CONTEST_ID =2 and STATUS ="WA"
)
,wa2 as (
select
    wa.USER_ID
    ,count() as WRONG_ANS
from
    wa
    INNER JOIN
    ac
    ON wa.USER_ID=ac.USER_ID and wa.PROBLEM_ID =ac.PROBLEM_ID and wa.ENTRY_ID =ac.ENTRY_ID
where 
    wa.SUBMITTED_AT < ac.SUBMITTED_AT
group by
    wa.USER_ID
)
,base as (
select
    ac.USER_ID
    ,sum(POINT) as POINT
    ,max(SUBMITTED_AT) as SUBMITTED_AT
    ,coalesce(WRONG_ANS,0) as WRONG_ANS
    ,max(STARTED_AT) as STARTED_AT
from 
    ac
    left join
    wa2
    on ac.USER_ID = wa2.USER_ID
    inner join
    ENTRIES as et
    on ac.ENTRY_ID = et.ENTRY_ID
group by
    ac.USER_ID
)
, base2 as (
select
    USER_ID
    ,POINT
    ,strftime("%s",SUBMITTED_AT)-strftime("%s",STARTED_AT) + WRONG_ANS*300 as EX_TIME
    ,WRONG_ANS
from 
    base
)
select
    rank() over(order by POINT desc,EX_TIME) as RANK
    ,USER_ID
    ,POINT
    ,EX_TIME
    ,WRONG_ANS
from
    base2