WITH B AS (
SELECT
  S.*
 ,E.STARTED_AT
FROM ENTRIES E
JOIN SUBMISSIONS S ON E.ENTRY_ID = S.ENTRY_ID
WHERE E.CONTEST_ID = 2
  AND S.CONTEST_ID = 2
  AND S.ENTRY_ID IS NOT NULL )

, AC AS (
SELECT
  ENTRY_ID
 ,MAX(strftime('%s', SUBMITTED_AT) - strftime('%s', STARTED_AT)) TIME
 ,SUM(POINT) POINT
FROM B
GROUP BY ENTRY_ID)

, MISS AS (
SELECT
  B1.ENTRY_ID
 ,COUNT(*) WRONG_ANS
FROM B B1
JOIN (
  SELECT PROBLEM_ID, USER_ID, MAX(SUBMITTED_AT) SUBMITTED_AT
  FROM B
  WHERE STATUS = 'AC'
  GROUP BY PROBLEM_ID, USER_ID
) B2
ON    B1.PROBLEM_ID = B2.PROBLEM_ID
  AND B1.USER_ID    = B2.USER_ID
  AND B1.SUBMITTED_AT < B2.SUBMITTED_AT
  AND B1.STATUS    <> 'AC'
GROUP BY B1.ENTRY_ID
)

SELECT
  RANK() OVER (ORDER BY POINT DESC, EX_TIME) RANK
 ,*
FROM (
SELECT
  E.USER_ID
 ,AC.POINT
 ,AC.TIME + (ifnull(MISS.WRONG_ANS, 0) * 300) EX_TIME
 ,ifnull(MISS.WRONG_ANS, 0) WRONG_ANS
FROM ENTRIES E
JOIN AC        ON E.ENTRY_ID = AC.ENTRY_ID
LEFT JOIN MISS ON E.ENTRY_ID = MISS.ENTRY_ID
WHERE E.CONTEST_ID = 2
)
ORDER BY 1,5,2