with sumpoint as (
  select
    s.USER_ID,
    sum(s.POINT) po
  from 
    SUBMISSIONS s
  where 
    s.CONTEST_ID = 2
    and s.ENTRY_ID is not null
  group by 
    s.USER_ID
)
, zikan1 as (
  select 
    e.USER_ID
    ,ss.at
    ,e.STARTED_AT
  from 
    (
    select
      s.USER_ID,
      max(ifnull(SUBMITTED_AT,0)) at
    from 
      SUBMISSIONS s
    where 
      s.CONTEST_ID = 2
      and STATUS = 'AC'
      and s.ENTRY_ID is not null
    group by 
      s.USER_ID
    ) ss
    ,ENTRIES e
  where 
    ss.USER_ID = e.USER_ID
    and e.CONTEST_ID = 2
    and e.ENTRY_ID is not null
)
, gotou as (
  select 
    s1.USER_ID
    ,count(SUBMITTED_AT) as gtcnt
  from 
    SUBMISSIONS s1
    inner join 
      zikan1
        on s1.USER_ID = zikan1.USER_ID
        and s1.CONTEST_ID = 2
  where 
    zikan1.at > s1.SUBMITTED_AT
    and s1.STATUS <> 'AC'
    and s1.ENTRY_ID is not null
  group by 
    s1.USER_ID
)
select
    RANK() OVER(
      ORDER BY
         sumpoint.po DESC,
         (zikan1.at - zikan1.STARTED_AT) + (ifnull(gotou.gtcnt,0)*300)
      ) as RANK
   ,sumpoint.USER_ID as USER_ID
   ,sumpoint.po as POINT
   ,strftime('%s', zikan1.at) - strftime('%s', zikan1.STARTED_AT) + (ifnull(gotou.gtcnt,0)*300) as EX_TIME
   ,ifnull(gotou.gtcnt,0) as WRONG_ANS
from
  sumpoint
  left join zikan1 on sumpoint.USER_ID = zikan1.USER_ID
  left join gotou on sumpoint.USER_ID = gotou.USER_ID
  
order by 
 RANK asc
 ,WRONG_ANS asc
 ,USER_ID asc