WITH PENALTY AS(

SELECT
 S.ENTRY_ID,
 COUNT(SUBMIT_ID) AS WRONG_ANS
FROM
 SUBMISSIONS AS S
 INNER JOIN (
 SELECT 
  ENTRY_ID,
  PROBLEM_ID,
  MAX(SUBMITTED_AT) AS LAST_SUBMITTED_AT
 FROM
  SUBMISSIONS
 WHERE
  STATUS = 'AC'
  AND ENTRY_ID IS NOT NULL
  AND CONTEST_ID = 2
 GROUP BY ENTRY_ID,
 PROBLEM_ID
 ) AS SUB1
    ON S.ENTRY_ID = SUB1.ENTRY_ID
    AND S.PROBLEM_ID = SUB1.PROBLEM_ID
    AND S.SUBMITTED_AT < SUB1.LAST_SUBMITTED_AT
    
GROUP BY S.ENTRY_ID
)

, SCORE_AND_TIME AS(
 SELECT
  ENTRY_ID,
  SUM(POINT) AS POINT,
  MAX(SUBMITTED_AT) AS LAST_SUBMITTED_AT
 FROM
  SUBMISSIONS AS S
 WHERE
  STATUS = 'AC'
  AND ENTRY_ID IS NOT NULL
  AND CONTEST_ID = 2
 GROUP BY 
  ENTRY_ID
)
SELECT
 RANK() OVER(
  ORDER BY
   POINT DESC,
   STRFTIME('%s',LAST_SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) + 5 * 60 * IFNULL(WRONG_ANS, 0) ASC
   ) AS RANK,
 E.USER_ID AS USER_ID,
 POINT AS POINT,
 STRFTIME('%s',LAST_SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) + 5 * 60 * IFNULL(WRONG_ANS, 0) AS EX_TIME,
 IFNULL(WRONG_ANS,0) AS WRONG_ANS
FROM
 ENTRIES AS E
 LEFT OUTER JOIN PENALTY AS P
  ON E.ENTRY_ID = P.ENTRY_ID JOIN SCORE_AND_TIME AS SAT
                              ON E.ENTRY_ID = SAT.ENTRY_ID
WHERE
 E.CONTEST_ID = 2
ORDER BY 
RANK ASC,
WRONG_ANS ASC,
USER_ID ASC;