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,
        ROUND((julianday(submitted_at) - julianday(started_at)) * 24 * 60 * 60) as time
    from sub s1
    where 1 = 1
    and status = 'AC'
    and ROUND((julianday(submitted_at) - julianday(started_at)) * 24 * 60 * 60) <= 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 * from point p
-- left outer join wrong_ans wa on p.user_id = wa.user_id
-- join time t on t.user_id = p.user_id

select
    rank() over(order by POINT desc, TIME + WRONG_ANS * 300 asc) RANK,
    USER_ID,
    POINT,
    TIME + WRONG_ANS * 300 EX_TIME,
    WRONG_ANS
from
(
    select
        p.user_id USER_ID,
        p.sum_point POINT,
        t.time TIME,
        IFNULL(wa.wrong_ans_count, 0) WRONG_ANS
    from point p
    left join wrong_ans wa on p.user_id = wa.user_id
    join time t on t.user_id = p.user_id
)temp
order by 1 asc, 5 asc, 2 asc
;