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 and s.contest_id = 2
    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
    having sum(point) > 0),
    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;