with user_results as (
    select
        S.user_id,
        sum(S.point) as total_point,
        sum(case when S.status = 'WA' then 1 else 0 end) as wrong_count,
        max(case when S.status = 'AC' then strftime('%s', S.submitted_at) else 0 end) -
            strftime('%s', E.started_at) +
            300 * sum(case when S.status = 'WA' then 1 else 0 end) as total_time
    from
        submissions S
        inner join entries E
        on S.entry_id = E.entry_id
    where
        S.contest_id = 2
        and S.entry_id is not null
        and exists (
            -- ペナルティ対象外の提出を除外する
            -- コンテスト中にACとなったものが存在するか
            select * from submissions S2
            where
                S2.entry_id is not null and
                S2.contest_id = S.contest_id and
                S2.user_id = S.user_id and
                S2.problem_id = S.problem_id and
                S2.status = 'AC' and
                S2.submitted_at >= S.submitted_at
        )
    group by
        S.user_id
    having
        total_point > 0
)
select
    rank() over (order by total_point desc, total_time) as RANK,
    user_id as USER_ID,
    total_point as POINT,
    total_time as EX_TIME,
    wrong_count as WRONG_ANS
from
    user_results
;