-- 得点生成
with TBL_AC as (
select
    S.CONTEST_ID
    ,S.USER_ID
    ,S.PROBLEM_ID
    ,S.POINT
    ,S.SUBMITTED_AT as AC_TIME
from
    SUBMISSIONS S
    inner join
        ENTRIES E
        on
            S.CONTEST_ID=E.CONTEST_ID
            and S.USER_ID=E.USER_ID
            and S.ENTRY_ID=E.ENTRY_ID
where
    -- ENTRY_ID is not null
    STATUS='AC'
    and S.CONTEST_ID='2'
)
,TBL_WA_CNT as (
-- 正解問題の誤答数と時間
select
    S.CONTEST_ID
    ,S.USER_ID
    ,S.PROBLEM_ID
    ,count(case when S.STATUS='WA' and 
        S.SUBMITTED_AT < AC_TIME then 1 end) as CNT_WA
from
    SUBMISSIONS S
    inner join TBL_AC A
        on
            S.CONTEST_ID=A.CONTEST_ID
            and S.USER_ID=A.USER_ID
            and S.PROBLEM_ID=A.PROBLEM_ID
group by 1,2,3
)
-- 合計の誤答数
,TBL_PENA as (
select
    CONTEST_ID
    ,USER_ID
    ,sum(CNT_WA) as CNT_WA
from
    TBL_WA_CNT
group by 1,2
)
-- 実回答時間
,TBL_TIME as (
select
    S.CONTEST_ID
    ,S.USER_ID
    ,max(strftime('%s', T.AC_TIME))
        -strftime('%s', S.STARTED_AT) as TIME_ActAns
from
    TBL_AC T
    inner join
        ENTRIES S 
        on
            T.CONTEST_ID=S.CONTEST_ID
            and T.USER_ID=S.USER_ID
group by 1,2
)
-- 点数の和
,TBL_POINT as (
select
    CONTEST_ID
    ,USER_ID
    ,sum(POINT) as POINT
from
    TBL_AC
where
    POINT > 0
group by 1,2
)
,TBL_EX_TIME as (
select
    P.CONTEST_ID
    ,P.USER_ID
    ,P.TIME_ActAns + 300*S.CNT_WA as EX_TIME
    ,S.CNT_WA as WRONG_ANS
    ,T.POINT
from
    TBL_TIME P 
    inner join
        TBL_PENA S
        on
            P.CONTEST_ID=S.CONTEST_ID
            and P.USER_ID=S.USER_ID
    inner join
        TBL_POINT T 
        on
            P.CONTEST_ID=T.CONTEST_ID
            and P.USER_ID=T.USER_ID
)
select 
    rank() over (partition by '' order by POINT desc, EX_TIME) 
        as RANK
    ,USER_ID
    ,POINT
    ,EX_TIME
    ,WRONG_ANS
from TBL_EX_TIME
order by RANK, WRONG_ANS desc, USER_ID
;