WITH PENALTY AS
(SELECT S.ENTRY_ID, COUNT(SUBMIT_ID) AS WRONG_ANS
FROM SUBMISSIONS S INNER JOIN
(SELECT ENTRY_ID, PROBLEM_ID, MAX(SUBMITTED_AT) AS LAST_SUBMITTED
FROM SUBMISSIONS S
WHERE CONTEST_ID = 2
AND STATUS = 'AC'
AND S.ENTRY_ID IS NOT NULL
GROUP BY ENTRY_ID, PROBLEM_ID) SUB
ON S.ENTRY_ID = SUB.ENTRY_ID
AND S.PROBLEM_ID = SUB.PROBLEM_ID
AND SUBMITTED_AT < LAST_SUBMITTED
GROUP BY S.ENTRY_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 ENTRY_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.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, WRONG_ANS, USER_ID