with 
    --開始時刻ひも付け用
    entry as (
    select ENTRY_ID,
            STARTED_AT 
    from ENTRIES
    where CONTEST_ID = 2
    ),
    
    --最後の正解ACはいつなのか（終了時刻）
    last_time as (
    select ENTRY_ID,
            MAX(SUBMITTED_AT) as "提出時刻"
    from SUBMISSIONS
    where CONTEST_ID = 2
        and ENTRY_ID is not null
        and STATUS = 'AC'
    group by ENTRY_ID
    ),
    
    es as (
    select USER_ID,
            sum(POINT) as "合計点",
            提出時刻,
            STARTED_AT as "開始時刻",
            sum(case STATUS when 'WA' then 1 else 0 end) as "誤答"
    from SUBMISSIONS inner join entry using(ENTRY_ID)
        inner join last_time using(ENTRY_ID)
    where SUBMITTED_AT <= 提出時刻
    group by USER_ID
    ),
    
    temp as (
    select 
        USER_ID,
        合計点 as "POINT",
        strftime('%s',提出時刻) - strftime('%s',開始時刻) + 5*60*誤答 as "EX_TIME",
        誤答 as "WRONG_ANS"
    from es
    
    )

select rank()OVER(order by POINT desc,EX_TIME asc) as "RANK",
        USER_ID,
        POINT,
        EX_TIME,
        WRONG_ANS
from temp
order by 1,5,2
;