with 
-- 必要なデータのみ取り出す
ac as (select *,strftime("%s",SUBMITTED_AT) as t from SUBMISSIONS where CONTEST_ID =2 and STATUS ="AC" and ENTRY_ID is not null)
,wa as (select * from SUBMISSIONS where CONTEST_ID =2 and STATUS ="WA" and ENTRY_ID is not null)
,et as (select *,strftime("%s",STARTED_AT) as t from ENTRIES where  CONTEST_ID =2)
-- 誤回答数
,wacnt as (
select
    wa.ENTRY_ID,
    count() as WRONG_ANS
from
    wa
    inner join
    ac
    on wa.entry_id = ac.entry_id and wa.PROBLEM_ID=ac.PROBLEM_ID
where
    wa.SUBMITTED_AT < ac.SUBMITTED_AT
group by
    wa.USER_ID
)
-- 集計
,base as (
select
    et.USER_ID 
    ,coalesce(sum(point),0) as POINT
    ,coalesce(max(ac.t),max(et.t)) - max(et.t) + coalesce(max(WRONG_ANS),0)*300 as EX_TIME
    ,coalesce(max(WRONG_ANS),0) as WRONG_ANS
from
    et
    left join
    ac
    on et.ENTRY_ID = ac.ENTRY_ID
    left join
    wacnt
    on et.ENTRY_ID = wacnt.ENTRY_ID
group by
    et.ENTRY_ID
)
--ランク付けして出力
select
    rank() over(order by POINT desc,EX_TIME) as `RANK`
    ,USER_ID
    ,POINT
    ,EX_TIME
    ,WRONG_ANS
from
    base
order by
    `RANK`
    ,WRONG_ANS
    ,USER_ID