with first_ac as (
    select
        s.USER_ID,
        s.PROBLEM_ID,
        min(s.SUBMITTED_AT) as SUBMITTED_AT
    from
        SUBMISSIONS as s
    where
        s.CONTEST_ID = 2
        and s.STATUS = "AC"
        and s.ENTRY_ID is not null
    group by
        1,2
)
,sub as (
    select
        s.ENTRY_ID,
        s.USER_ID,
        sum(s.POINT) as POINT,
        max(case when s.STATUS = "AC" then s.SUBMITTED_AT end) as SUBMITTED_AT,
        count(case when s.STATUS = "WA" then 1 end) as WRONG_ANS
    from
        SUBMISSIONS as s
    inner join
        first_ac as f
        on s.USER_ID = f.USER_ID
            and s.PROBLEM_ID = f.PROBLEM_ID
            and s.SUBMITTED_AT <= f.SUBMITTED_AT
    where
        s.CONTEST_ID = 2
        and s.ENTRY_ID is not null
    group by
        1,2
    having
        POINT > 0
)
select
    rank() over (order by s.POINT desc, strftime('%s', s.SUBMITTED_AT) - strftime('%s', e.STARTED_AT) asc) as RANK, --順位(1から順にカウントアップ)
    e.USER_ID as USER_ID,
    s.POINT as POINT,
    strftime('%s', s.SUBMITTED_AT) - strftime('%s', e.STARTED_AT) + WRONG_ANS * 300 as EX_TIME,
    s.WRONG_ANS as WRONG_ANS
from
    ENTRIES as e
inner join
    sub as s
    on e.ENTRY_ID = s.ENTRY_ID
        and e.USER_ID = s.USER_ID
where
    e.CONTEST_ID = 2
order by
    1,5,2