WITH PENALTY AS
(SELECT S.USER_ID, COUNT(SUBMIT_ID) AS WRONG_ANS
FROM SUBMISSIONS S INNER JOIN
(SELECT S.USER_ID, PROBLEM_ID, MAX(SUBMITTED_AT) AS LAST_SUBMITTED
FROM SUBMISSIONS S INNER JOIN ENTRIES E
ON S.USER_ID = E.USER_ID
WHERE S.CONTEST_ID = 2
AND STATUS = 'AC'
AND S.ENTRY_ID IS NOT NULL
GROUP BY S.USER_ID, PROBLEM_ID) SUB
ON S.USER_ID = SUB.USER_ID
AND SUBMITTED_AT < LAST_SUBMITTED
GROUP BY S.USER_ID),
SCORE_AND_TIME AS
(SELECT ENTRY_ID, SUM(POINT) AS POINT,
MAX(SUBMITTED_AT) AS LAST_SUBMITTED
FROM SUBMISSIONS
WHERE STATUS = 'AC'
AND ENTRY_ID IS NOT NULL
AND CONTEST_ID = 2
GROUP BY USER_ID)
SELECT RANK() OVER(ORDER BY POINT DESC,
STRFTIME('%s', LAST_SUBMITTED) - STRFTIME('%s', STARTED_AT) + 300 * IFNULL(WRONG_ANS, 0) ASC) AS RANK,
E.USER_ID AS USER_ID,
POINT,
STRFTIME('%s', LAST_SUBMITTED) - STRFTIME('%s', STARTED_AT) + 300 * IFNULL(WRONG_ANS, 0) AS EX_TIME,
IFNULL(WRONG_ANS, 0) AS WRONG_ANS
FROM ENTRIES AS E
LEFT JOIN PENALTY AS P
ON E.USER_ID = P.USER_ID
JOIN SCORE_AND_TIME AS SAT
ON E.ENTRY_ID = SAT.ENTRY_ID
WHERE E.CONTEST_ID = 2
ORDER BY RANK, WRONG_ANS, USER_ID