with ct1 as (
    select entry_id, max(sa) as last_submit
    from (
        select entry_id, min(submitted_at) as sa 
        from SUBMISSIONS
        where entry_id is not null and status = "AC"
        group by entry_id, problem_id
        )
    group by entry_id
),
ct2 as(select entry_id, problem_id from submissions
where status = "AC"),


ct3 as(select s.contest_id, s.problem_id, s.user_id, s.entry_id, s.submitted_at, s.status, s.point from
submissions s join ct1 c1 on c1.entry_id = s.entry_id join ct2 on ct2.entry_id = s.entry_id
where s.submitted_at <= last_submit and s.problem_id = ct2.problem_id
order by s.user_id, s.submitted_at),

ct4 as (select entry_id, sum(point) as point, sum(case when status = "WA" then 1 else 0 end) wrong_ans
from ct3
group by entry_id),

ct5 as(select c1.entry_id, e.user_id, (strftime('%s', c1.last_submit) - strftime('%s', e.started_at) + (c4.wrong_ans * 300)) as ex_time
from ct1 c1 join entries e on e.entry_id = c1.entry_id join ct4 c4 on c4.entry_id = e.entry_id)

select rank() over(order by c4.point desc, c5.ex_time, c4.wrong_ans) "RANK",c5.user_id "USER_ID", c4.point "POINT", c5.ex_time "EX_TIME", c4.wrong_ans "WRONG_ANS"
from ct5 c5 join ct4 c4 on c5.entry_id = c4.entry_id
order by c4.point desc, c5.ex_time, c4.wrong_ans, c5.user_id;