select rank() over (
    order by
        ac.point desc,ex_time + pena
    )                 as RANK
        ,
       ac.USER_ID,
       ac.POINT,
       EX_TIME + pena as EX_TIME,
       WRONG_ANS
from (select user_id,sum(point) as point
      from SUBMISSIONS
      where ENTRY_ID is not null
        and CONTEST_ID = 2
        and status = 'AC'
      group by user_id
      
      ) as ac
         inner join

     (with ac as (select *
                  from SUBMISSIONS
                  where ENTRY_ID is not null
                    and CONTEST_ID = 2
                    and status = 'AC')
      select ENTRIES.USER_ID                                                           as USER_ID,
             -- sum(ac.POINT) as POINT,
             strftime('%s', max(ac.submitted_at)) - strftime('%s', ENTRIES.STARTED_AT) as EX_TIME,
             count(wa.SUBMIT_ID) * 300                                                 as pena,
             count(wa.SUBMIT_ID)                                                       as WRONG_ANS

      from ENTRIES
               inner join ac
                          on ENTRIES.ENTRY_ID = ac.ENTRY_ID
               left outer join
           SUBMISSIONS wa
           on ac.CONTEST_ID = wa.CONTEST_ID
               and ac.PROBLEM_ID = wa.PROBLEM_ID
               and ac.USER_ID = wa.USER_ID
               and wa.submitted_at < ac.submitted_at
               and wa.status = 'WA'
               and wa.entry_id is not null
      where ENTRIES.CONTEST_ID = 2
      group by ENTRIES.USER_ID) wa
     on ac.USER_ID = wa.USER_ID
order by 1,5,2
;