with e as (
    select
        user_id
        ,started_at
    from
        entries
    where
        contest_id = 2
),
s as (
    select
        distinct user_id
        ,max(submitted_at) over (partition by user_id) as last_time
        ,sum(point) over (partition by user_id) as points
    from
        submissions
    where
        contest_id = 2
        and status = 'AC'
        and entry_id is not null
),
pt as (
    select
        e.user_id
        ,strftime('%s', s.last_time) - strftime('%s', e.started_at) as ex_time
        ,s.points
    from
        e
        join
            s
            on e.user_id = s.user_id
),
ca as (
    select
        user_id
        ,submitted_at
        ,problem_id
    from
        submissions
    where
        contest_id = 2
        and entry_id is not null
        and status = 'AC'
),
wa as (
    select
        user_id
        ,submitted_at
        ,problem_id
    from
        submissions
    where
        contest_id = 2
        and entry_id is not null   
        and status != 'AC'
),
wp as (
    select
        ca.user_id
        ,count(wa.problem_id) as wrong_num
    from
        ca
        left join
            wa
            on
                ca.user_id = wa.user_id
                and ca.problem_id = wa.problem_id
                and ca.submitted_at > wa.submitted_at
    group by
        1

)

select
    rank() over (order by pt.points desc, pt.ex_time + wp.wrong_num * 300 asc) as RANK
    ,pt.user_id as USER_ID
    ,pt.points as POINT
    ,pt.ex_time + wp.wrong_num * 300 as EX_TIME
    ,wp.wrong_num as WRONG_ANS
from    
    pt
    left join
        wp
        on
            pt.user_id = wp.user_id
where
    pt.points > 0
order by
    1, 5, 2