with cte as (
    select
        s.user_id
        , s.problem_id
        , s.submitted_at
        , s.status
        , s.point
        , s.entry_id
        , e.started_at
    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
)

select
    rank() over(order by a.point desc, a.ans_time + (300 * wrong_cnt)) as 'RANK'
    , a.user_id as 'USER_ID'
    , a.point as 'POINT'
    , a.ans_time + (300 * wrong_cnt) as 'EX_TIME'
    , b.wrong_cnt as 'WRONG_ANS'
from
(
    select 
        user_id
        , max(strftime('%s', submitted_at)) - min(strftime('%s', started_at)) as 'ans_time'
        , sum(point) as 'point'
    from
        cte
    group by
        user_id
) a
inner join (
    select
        user_id,
        sum(wrong_cnt) as 'wrong_cnt'
    from
    (
        select
            user_id,
            problem_id,
            min(rownum) - 1 as wrong_cnt
        from (
            select
                user_id,
                problem_id,
                status,
                row_number() over(partition by user_id, problem_id order by submitted_at) as rownum
            from
                submissions
        )
        where 
            status = 'AC'
        group by
            user_id, problem_id
    )
    group by
        user_id
) b
on 
    a.user_id = b.user_id
order by
    rank() over(order by a.point desc, a.ans_time + (300 * wrong_cnt))
    , b.wrong_cnt
    , a.user_id