-- 合計得点
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
;


-- 解答時間、誤答数
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
)
, prob_last_sub as (
select 
    L.USER_ID
    ,L.PROBLEM_ID
    ,max(case when STATUS = 'AC' then DIFF_ST_SUB else null end) as EX_TIME_PROB
from base as L
group by 
    USER_ID, PROBLEM_ID
)
, prob_wrong_ans as(
select 
    L.USER_ID
    ,L.PROBLEM_ID
    ,sum(case when STATUS != 'AC' and EX_TIME_PROB is not null and  DIFF_ST_SUB < EX_TIME_PROB then 1 else 0 end) as WRONG_ANS_PROB
from base as L
left join prob_last_sub as R using(USER_ID, PROBLEM_ID)
group by 
    USER_ID, PROBLEM_ID
)
,wrong_ans as(
select 
    USER_ID
    ,sum(WRONG_ANS_PROB) as WRONG_ANS
from prob_wrong_ans
group by 
    USER_ID
)
select 
    rank() over(order by L.POINT desc, R1.EX_TIME + R2.WRONG_ANS*300) as RANK
    ,L.USER_ID
    ,L.POINTD
    ,R1.EX_TIME + R2.WRONG_ANS*300 as EX_TIME
    ,R2.WRONG_ANS
from total_point as L
left join last_sub_ac as R1 using(USER_ID)
left join wrong_ans as R2 using(USER_ID)
order by RANK, R2.WRONG_ANS, L.USER_ID
;