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
    ),
    
    --上記2テーブルを提出履歴と内部結合
    es as (
    select USER_ID,
            POINT,
            提出時刻,
            STARTED_AT as "開始時刻",
            (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 <= 提出時刻
    ),
    
    --集約
    es_sum as (
    select USER_ID,
            sum(POINT) as "合計点",
            sum(誤答フラグ) as "WRONG_ANS",
            max(提出時刻) as 提出時刻,
            max(開始時刻) as 開始時刻
    from es
    group by USER_ID
    ),
    
    temp as (
    select 
        USER_ID,
        合計点 as "POINT",
        strftime('%s',提出時刻) - strftime('%s',開始時刻) + 5*60*WRONG_ANS as "EX_TIME",
        WRONG_ANS
    from es_sum
    )
    
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
;