WITH ENT2 as (
select
  ENTRY_ID
  , USER_ID
  , STARTED_AT
from ENTRIES
where
  CONTEST_ID = 2
  and ENTRY_ID is not NULL
  and USER_ID is not NULL
), SUB2 as (
    select
      USER_ID
      , ENTRY_ID
      , PROBLEM_ID
      , SUBMIT_ID
      , SUBMITTED_AT
      , STATUS
      , POINT
    from SUBMISSIONS
    where CONTEST_ID = 2
      and ENTRY_ID is not NULL
      and USER_ID is not NULL
), J as (
select
 SUB2.USER_ID as UID
 , SUB2.ENTRY_ID as EID
 , PROBLEM_ID
 , SUBMITTED_AT
 , STARTED_AT
 , cast(strftime('%s', SUBMITTED_AT) as integer) - cast(strftime('%s', STARTED_AT) as integer) as elapsed
 , STATUS
 , POINT
from SUB2 LEFT JOIN ENT2
  on SUB2.ENTRY_ID = ENT2.ENTRY_ID
 and SUB2.USER_ID and ENT2.USER_ID
order by SUB2.USER_ID, SUB2.ENTRY_ID, POINT
), ACT as (
    select
      UID,
      EID,
      PROBLEM_ID,
      MIN(elapsed) as AC_TIME
    from J
    where STATUS = 'AC'
    group by UID, EID, PROBLEM_ID
), S as (
select
  J.UID as USER_ID
  , SUM(POINT) as POINT
  , MAX(CASE WHEN STATUS = 'AC' THEN elapsed ELSE -99 END) as LAST_AC
  , MAX(ACT.AC_TIME) as LAST_AC_TIME
  , SUM(STATUS <> 'AC' and elapsed < ACT.AC_TIME) as WRONG_ANS
from J LEFT JOIN ACT
 on J.UID = ACT.UID and J.EID = ACT.EID and J.PROBLEM_ID = ACT.PROBLEM_ID
group by J.UID
order by POINT desc
)
select
  RANK() over (order by POINT desc, (LAST_AC_TIME + 300*WRONG_ANS) asc, USER_ID asc) as RANK
  , USER_ID
  , POINT
  , (LAST_AC_TIME + 300*WRONG_ANS) as EX_TIME
  , WRONG_ANS
from S
where POINT > 0