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 SUBMISSIONS.user_id,sum(point) as point
      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

      ) as ac
         inner join
     (with ac 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')
      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
;