with ac as (
  select
      USER_ID
    , PROBLEM_ID
    , MAX(SUBMITTED_AT) as AC_AT
    , SUM(POINT)        as POINT
  from
      SUBMISSIONS
  where
      CONTEST_ID = 2 
  and STATUS     = 'AC'
  and ENTRY_ID   IS NOT NULL
  group by
      USER_ID
    , PROBLEM_ID
), wa as (
  select
      s.USER_ID
    , s.PROBLEM_ID
    , COUNT(s.USER_ID) as WA_COUNT
  from 
      SUBMISSIONS s
  inner join      ac
          on  s.USER_ID    = ac.USER_ID
          and s.PROBLEM_ID = ac.PROBLEM_ID
  where
      s.CONTEST_ID   =  2 
  and s.ENTRY_ID     IS NOT NULL
  and s.SUBMITTED_AT <  ac.AC_AT
  and s.STATUS       <> 'AC'
  group by 
      s.USER_ID
    , s.PROBLEM_ID
  
), clear_time as (
  select
      e.USER_ID
    , MAX(strftime('%s', ac.AC_AT) - strftime('%s', e.STARTED_AT) ) as EX_TIME_WITHOUT_WA
  from 
     ENTRIES e
  inner join ac
          on e.USER_ID = ac.USER_ID
  where
      e.CONTEST_ID   =  2 
  group by 
      e.USER_ID

), joined as (
  select
      ac.USER_ID
    , cl.EX_TIME_WITHOUT_WA
    , SUM(ac.POINT)                 as POINT
    , coalesce(SUM(wa.WA_COUNT), 0) as WRONG_ANS
  from
             ac
  left outer join wa
          on  wa.USER_ID    = ac.USER_ID
          and wa.PROBLEM_ID = ac.PROBLEM_ID
  inner join clear_time cl
          on            cl.USER_ID    = ac.USER_ID
  group by
      ac.USER_ID
), summary as (
  select
      USER_ID
    , POINT
    , EX_TIME_WITHOUT_WA + 300 * WRONG_ANS as EX_TIME
    , WRONG_ANS
  from joined
)
select
    rank() over(order by POINT desc, EX_TIME asc) as RANK
  , USER_ID
  , POINT
  , EX_TIME
  , WRONG_ANS
from
    summary
where
    POINT <> 0
order by
    RANK      asc
  , WRONG_ANS asc
  , USER_ID   asc