with
  a as (
    select
      entry_id,
      sum(point) as P,
      max(
        case status
          when 'AC' then submitted_at
          else null
        end
      ) max_t
    from
      SUBMISSIONS
    where
      contest_id = 2
      and entry_id is not null
    group by
      entry_id
  ),
  c as (
    select
      sub.entry_id,
      count(*) as cnt
    from
      SUBMISSIONS sub
      join SUBMISSIONS b on sub.entry_id = b.entry_id
      and sub.problem_id = b.problem_id
      and sub.submitted_at < b.submitted_at
      and b.status = 'AC'
    where
      sub.contest_id = 2
      and sub.entry_id is not null
      and sub.status <> 'AC'
    group by
      sub.entry_id
  )
select
  RANK() OVER (
    ORDER BY
      POINT DESC,
      EX_TIME
  ) as 'RANK',
  USER_ID,
  POINT,
  EX_TIME,
  WRONG_ANS
from
  (
    select
      sub.USER_ID,
      max(a.P) as POINT,
      max(
        strftime('%s', max_t) - strftime('%s', ent.STARTED_AT) + ifnull(c.cnt, 0) * 300
      ) as EX_TIME,
      max(ifnull(c.cnt, 0)) as WRONG_ANS
    from
      submissions sub
      join a on sub.entry_id = a.entry_id
      left join c on sub.entry_id = c.entry_id
      join entries ent on sub.entry_id = ent.entry_id
      and ent.contest_id = 2
    where
      sub.entry_id is not null
      and a.max_t is not null
    group by
      sub.user_id
  ) a
order by
  1,
  WRONG_ANS,
  2