with org as (
select
    *
from submissions as a
inner join (
    select
        entry_id
        , problem_id
        , max(submitted_at) last_submitted_at
    from submissions as b
    where b.status = 'AC'
        and b.contest_id = 2
        and b.entry_id is not null
    group by entry_id, problem_id
) as c
    on a.entry_id = c.entry_id
        and a.problem_id = c.problem_id
        and a.submitted_at < c.last_submitted_at
), org2 as (
    select
        entry_id
        , count(submit_id) as num_wrong_ans
    from org
    group by 1
), score_and_time as (
    select
        entry_id
        , sum(point) as sum_point
        , max(submitted_at) as last_submitted
    from submissions
    where contest_id = 2
        and entry_id is not null
        and status = 'AC'
    group by 1
)
select
    rank() over (order by sum_point desc, strftime('%s', last_submitted) - strftime('%s', started_at) + 300 * ifnull(num_wrong_ans, 0)) as RANK
    , E.user_id as USER_ID
    , sum_point as POINT
    , strftime('%s', last_submitted) - strftime('%s', started_at) + 300 * ifnull(num_wrong_ans, 0) as EX_TIME
    , ifnull(num_wrong_ans, 0) as WRONG_ANS
from entries as E
left outer join org2
    on E.entry_id = org2.entry_id
join score_and_time as SAT
    on E.entry_id = SAT.entry_id
where E.contest_id = 2
order by RANK, org2.num_wrong_ans, E.user_id
;