with sub as (
    select
        s.*,
        e.started_at
    from SUBMISSIONS s
    join ENTRIES e on s.entry_id = e.entry_id
    where s.contest_id = 2
),
point as (
    select
        user_id,
        sum(point) sum_point
    from sub
    where 1 = 1
    and status = 'AC'
    group by user_id
),
wrong_ans as (
    select
        user_id,
        count(*) wrong_ans_count
    from sub s1
    where 1 = 1
    and status = 'WA'
    and exists (
        select * from sub s2
        where s1.user_id = s2.user_id
        and s1.problem_id = s2.problem_id
        and s2.status = 'AC'
        and s1.submitted_at < s2.submitted_at
    )
    group by user_id
),
time as (
    select
        user_id,
        --DATETIME(submitted_at, -started_at) as 実回答時間
        --submitted_at,
        --started_at,
        CAST((julianday(submitted_at) - julianday(started_at)) * 24 * 60 * 60 AS INTEGER) as time
    from sub s1
    where 1 = 1
    and status = 'AC'
    and CAST((julianday(submitted_at) - julianday(started_at)) * 24 * 60 * 60 AS INTEGER) <= 60*60
    and submitted_at = (
            select max(s2.submitted_at) 
            from sub s2 
            where s1.user_id = s2.user_id and s2.status = 'AC'
        )
)

select
    rank() over(order by p.sum_point desc, t.time + wa.wrong_ans_count * 300 asc) RANK,
    p.user_id USER_ID,
    p.sum_point POINT,
    t.time + wa.wrong_ans_count * 300 as EX_TIME,
    wa.wrong_ans_count WRONG_ANS
from point p
join wrong_ans wa on p.user_id = wa.user_id
join time t on t.user_id = wa.user_id
;