with submissions_extra as (
    select
        E.started_at,
        S.user_id,
        S.problem_id,
        S.submitted_at,
        S.status,
        S.point,
        min(case when S.status = 'AC' then S.submitted_at else null end) over (partition by S.user_id, S.problem_id) as first_ac_time
    from
        -- 提出していない人を考慮するため、entriesとouter joinする
        entries E
        left outer join submissions S
        on E.entry_id = S.entry_id
),
user_results as (
    select
        user_id,
        sum(point) as total_point,
        max(strftime('%s', case when status = 'AC' then submitted_at else null end)) - max(strftime('%s', started_at)) +
            300 * sum(case when status = 'WA' and submitted_at < first_ac_time then 1 else 0 end) as ex_time,
        sum(case when status = 'WA' and submitted_at < first_ac_time then 1 else 0 end) as wrong_count
    from
        submissions_extra
    group by
        user_id
    having
        total_point > 0
)
select
    rank() over (order by total_point desc, ex_time) as RANK,
    user_id as USER_ID,
    total_point as POINT,
    ex_time as EX_TIME,
    wrong_count as WRONG_ANS
from
    user_results
order by
    rank
;