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