-- 合計得点
create temp table total_point as 
select user_id, sum(POINT) as POINT
from SUBMISSIONS
where CONTEST_ID = 2
and ENTRY_ID is not null
group by user_id 
having sum(POINT) > 0
;


-- 解答時間、誤答数
create temp table EX_WRONG_ANS as 
with 
base as(
select 
    R.USER_ID
    ,strftime('%s', L.STARTED_AT) as STARTED_AT_UNX
    ,strftime('%s', R.SUBMITTED_AT) as SUBMITTED_AT_UNX
    ,strftime('%s', R.SUBMITTED_AT) - strftime('%s', L.STARTED_AT) as DIFF_ST_SUB
    ,PROBLEM_ID
    ,STATUS
from ENTRIES as L
left join SUBMISSIONS as R using(ENTRY_ID)
where R.CONTEST_ID = 2
and R.ENTRY_ID is not null
)
, last_sub_ac as(
select 
    USER_ID
    ,max(case when STATUS = 'AC' then DIFF_ST_SUB else null end) as EX_TIME
from base 
group by USER_ID
)
select 
    L.USER_ID
    ,R.EX_TIME
    ,sum(case when L.DIFF_ST_SUB <= EX_TIME and STATUS != 'AC' then 1 else 0 end) as WRONG_ANS
from base as L
left join last_sub_ac as R using(USER_ID)
group by L.USER_ID, R.EX_TIME
;

select 
    rank() over(order by L.POINT desc, R.EX_TIME + R.WRONG_ANS*300, R.WRONG_ANS) as RANK
    ,L.USER_ID
    ,L.POINT
    ,R.EX_TIME + R.WRONG_ANS*300 AS EX_TIME
    ,R.WRONG_ANS
from total_point as L
left join EX_WRONG_ANS as R using(USER_ID)
;