WITH
prepro AS(
SELECT
    *,
    MAX(CASE WHEN STATUS = "AC" THEN SUBMITTED_AT END) OVER (PARTITION BY USER_ID,PROBLEM_ID) AS LATEST_AC
FROM SUBMISSIONS
LEFT JOIN ENTRIES
USING(ENTRY_ID,CONTEST_ID,USER_ID)
WHERE
    ENTRY_ID IS NOT NULL
AND
    CONTEST_ID = 2
), result AS(
SELECT
    USER_ID,
    SUM(POINT) AS POINT,
    COUNT(DISTINCT CASE WHEN SUBMITTED_AT < LATEST_AC AND STATUS="WA" THEN SUBMIT_ID END) AS WRONG_ANS,
    STRFTIME("%s",MAX(CASE WHEN STATUS = "AC" THEN SUBMITTED_AT END)) - STRFTIME("%s",MIN(STARTED_AT)) AS EX_TIME_PRE
FROM prepro
GROUP BY USER_ID
)
SELECT
    RANK,
    USER_ID,
    POINT,
    EX_TIME,
    IFNULL(WRONG_ANS,0) WRONG_ANS
FROM (
SELECT
    *,
    EX_TIME_PRE + 300 * IFNULL(WRONG_ANS,0) AS EX_TIME,
    RANK() OVER(ORDER BY POINT DESC,EX_TIME_PRE + 300 * IFNULL(WRONG_ANS,0)) AS RANK
FROM result
WHERE
    POINT > 0
)
ORDER BY RANK,WRONG_ANS,USER_ID