--最初のAC情報。
with FIRST_AC_TIME as (
select
    E.USER_ID,
    S.PROBLEM_ID,
    min(S.SUBMITTED_AT) as AC_TIME
from
    ENTRIES E
inner join
    SUBMISSIONS S
on
    E.CONTEST_ID = 2
    and
    E.CONTEST_ID = S.CONTEST_ID
    and
    E.USER_ID = S.USER_ID
    and
    S.STATUS = 'AC'
    and
    S.SUBMITTED_AT < datetime(E.STARTED_AT, '+1 hours')
group by
    E.USER_ID,
    S.PROBLEM_ID
),PER_PROBLEM as (
--問題単位で集計する。ACしていない問題は除外
select
    E.USER_ID,
    S.PROBLEM_ID,
    min(E.STARTED_AT) as STARTED_AT,
    sum(case when S.STATUS = 'WA' then 1 else 0 end) as WA,
    sum(S.POINT) as POINT,
    max(case when S.STATUS = 'AC' then S.SUBMITTED_AT end) as LAST_AC
from
    ENTRIES E
inner join
    SUBMISSIONS S
on
    E.CONTEST_ID = 2
    and
    E.CONTEST_ID = S.CONTEST_ID
    and
    E.USER_ID = S.USER_ID
    and
    S.SUBMITTED_AT < datetime(E.STARTED_AT, '+1 hours')
inner join
    FIRST_AC_TIME F
on
    S.USER_ID = F.USER_ID
    and
    S.PROBLEM_ID = F.PROBLEM_ID
    and
    S.SUBMITTED_AT <= F.AC_TIME
group by
    E.USER_ID,
    S.PROBLEM_ID
),RESULT as (
select
    USER_ID,
	strftime('%s', max(LAST_AC)) - strftime('%s', min(P.STARTED_AT)) + sum(WA) * 300 as EX_TIME,
	sum(POINT) as POINT,
	sum(WA) as WRONG_ANS
from
    PER_PROBLEM P
group by
    USER_ID
)

select
    rank() over (order by POINT desc, EX_TIME asc) as RANK,
    USER_ID,
    EX_TIME,
    POINT,
    WRONG_ANS
from
    RESULT
order by
    RANK asc,
    WRONG_ANS asc,
    USER_ID asc