with T as (
    select
        user_id,
        problem_id,
        (
            select 
                submitted_at 
            from Submissions s2 
            where s2.user_id = s1.user_id 
            and s2.problem_id = s1.problem_id 
            and status = 'AC' 
            and contest_id = 2
            and entry_id is not null
            order by submitted_at desc 
            limit 1
        ) first_ac_time
    from Submissions s1
    where contest_id = 2
    and entry_id is not null
    group by user_id, problem_id
),
T2 as (
    select 
        user_id,
        problem_id,
        (
            case when first_ac_time is null
                then 0
                else (
                    select 
                        count(*) 
                    from Submissions s 
                    where s.user_id = t.user_id 
                    and s.problem_id = t.problem_id 
                    and s.contest_id = 2
                    and s.entry_id is not null
                    and status = 'WA'
                    and submitted_at < first_ac_time
                )
            end
        ) wa_count,
        (
            case when first_ac_time is null 
                then 0
                else (
                    select 
                        point
                    from Submissions s 
                    where s.user_id = t.user_id 
                    and s.problem_id = t.problem_id 
                    and s.contest_id = 2
                    and s.entry_id is not null
                    and status = 'AC'
                    limit 1
                )
            end
        ) got_point,
        first_ac_time
    from T t
),
T3 as (
    select
        user_id,
        sum(got_point) point,
        sum(wa_count) wrong_ans,
        max(first_ac_time) last_problem_ac_time, 
        (
            select 
                started_at
            from Entries e 
            where e.user_id = t.user_id
            and contest_id = 2
            limit 1
        ) started_at
    from T2 t
    group by user_id
    having last_problem_ac_time is not null
),
T4 as (
    select
        user_id,
        unixepoch(last_problem_ac_time) - unixepoch(started_at) ex_time,
        point,
        wrong_ans
    from T3 t
)
select
    rank() over(order by point desc, ex_time) RANK,
    user_id USER_ID,
    point POINT,
    ex_time EX_TIME,
    wrong_ans WRONG_ANS
from T4
order by RANK, WRONG_ANS, USER_ID;
-- select * from Submissions where user_id = '118'
-- select * from T where user_id = '118'