with contest_submissions as (
    select
        S.*,
        E.started_at
    from
        submissions S
        inner join entries E
        using (entry_id, user_id)
    where
        S.contest_id = 2
),
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', S.started_at) +
            300 * sum(case when S.status = 'WA' then 1 else 0 end) as total_time
    from
        contest_submissions S
    where
        exists (
            -- ペナルティ対象外の提出を除外する
            -- コンテスト中にACとなったものが存在するか
            select * from contest_submissions S2
            where
                S2.entry_id = S.entry_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
order by
    rank,
    wrong_count,
    user_id
;