with ac_d as (select SUBMISSIONS.*
              from SUBMISSIONS
                       inner join entries
                                  on entries.entry_id = submissions.entry_id
              where entries.CONTEST_ID = 2
                and point > 0
                and status = 'AC'),
     ac_sum as (select SUBMISSIONS.user_id,
                       sum(point)                                                                  as point,
                       strftime('%s', max(submitted_at)) - strftime('%s', min(ENTRIES.STARTED_AT)) as EX_TIME
                from SUBMISSIONS
                         inner join entries
                                    on entries.entry_id = submissions.entry_id
                where entries.CONTEST_ID = 2
                  and status = 'AC'
                  and point > 0
                group by submissions.user_id)
select rank() over (
    order by
        ac_sum.point desc,ex_time + coalesce(pena, 0)
    )                              as RANK,
       ac_sum.USER_ID USER_ID,
       ac_sum.POINT POINT,
       EX_TIME + coalesce(pena, 0) as EX_TIME,
       coalesce(WRONG_ANS, 0) as WRONG_ANS
from ac_sum
         left join (select ENTRIES.USER_ID                        as USER_ID,
                           count(wa.SUBMIT_ID) * 300 as pena,
                           count(wa.SUBMIT_ID)       as WRONG_ANS
                    from ENTRIES
                             inner join SUBMISSIONS wa
                                        on ENTRIES.entry_id = wa.entry_id
                                            and wa.status <> 'AC'
                             inner join ac_d
                                        on ac_d.entry_id = entries.entry_id
                                            and ac_d.contest_ID = wa.contest_ID
                                            and ac_d.PROBLEM_ID = wa.PROBLEM_ID
                                            and ac_d.USER_ID = wa.USER_ID
                    where ENTRIES.CONTEST_ID = 2
                      and wa.submitted_at < ac_d.submitted_at
                    group by ENTRIES.USER_ID) wa
                   on ac_sum.user_id = wa.USER_ID
order by 1, 5, 2
;