WITH A as (
  SELECT
    distinct
    strftime('%s', sub.SUBMITTED_AT) - strftime('%s', en.STARTED_AT) time_diff
    , sub.*
    , count(sub_wa.status != 'AC') over (partition by sub.SUBMIT_ID, sub.CONTEST_ID, sub.PROBLEM_ID, sub.ENTRY_ID) wrong
  FROM
    ENTRIES en
      INNER JOIN SUBMISSIONS sub ON en.ENTRY_ID = sub.ENTRY_ID
      LEFT JOIN SUBMISSIONS sub_wa ON en.ENTRY_ID = sub_wa.ENTRY_ID and sub.PROBLEM_ID = sub_wa.PROBLEM_ID and sub_wa.STATUS != 'AC' and sub_wa.SUBMITTED_AT < sub.SUBMITTED_AT
  WHERE
    en.CONTEST_ID = '2'
    and sub.STATUS = 'AC'
  ORDER BY
    sub.SUBMITTED_AT DESC
), B as (
  SELECT
    sum(A.time_diff) + (A.wrong) * 300 EX_TIME
    , A.USER_ID USER_ID
    , sum(A.POINT) POINT
    , sum(A.wrong) WRONG_ANS
  FROM
    A
  GROUP BY
    A.USER_ID
)
SELECT
  RANK() over (order by POINT desc, EX_TIME) RANK
  , USER_ID
  , POINT
  , EX_TIME
  , WRONG_ANS
FROM
  B
ORDER BY
  RANK, WRONG_ANS, USER_ID
;