WITH data AS (
    SELECT
        *
    FROM ENTRIES
    JOIN SUBMISSIONS
    USING (CONTEST_ID, USER_ID, ENTRY_ID)
    WHERE
    ENTRY_ID IS NOT NULL
    AND CONTEST_ID = 2
), points AS (
    SELECT
        USER_ID,
        SUM(POINT) POINT
    FROM data
    GROUP BY USER_ID
    HAVING POINT > 0
), max_time AS (
    SELECT
        USER_ID,
        MAX(SUBMITTED_AT) MAX_SUBMITTED_AT
    FROM data
    WHERE STATUS = "AC"
    GROUP BY USER_ID
), ac AS (
    SELECT
        USER_ID,
        MAX(strftime('%s', MAX_SUBMITTED_AT) - strftime('%s', STARTED_AT)) AS TIME
    FROM data
    JOIN max_time
    USING (USER_ID)
    WHERE STATUS = "AC"
    GROUP BY USER_ID
), max_time_prob AS (
    SELECT
        USER_ID,
        PROBLEM_ID,
        MAX(SUBMITTED_AT) MAX_SUBMITTED_AT
    FROM data
    WHERE STATUS = "AC"
    GROUP BY USER_ID, PROBLEM_ID
), wrong AS (
    SELECT
        USER_ID,
        COUNT(CASE WHEN SUBMITTED_AT < MAX_SUBMITTED_AT THEN 1 END) AS WRONG_ANS
    FROM data
    JOIN max_time_prob
    USING (USER_ID, PROBLEM_ID)
    WHERE STATUS != "AC"
    GROUP BY USER_ID
)

SELECT

RANK() OVER (ORDER BY POINT DESC, TIME + 300 * IFNULL(WRONG_ANS, 0)) RANK,
USER_ID,
POINT,
TIME + 300 * IFNULL(WRONG_ANS, 0) AS EX_TIME,
IFNULL(WRONG_ANS, 0) WRONG_ANS
FROM points
JOIN ac
USING (USER_ID)
LEFT OUTER JOIN wrong
USING (USER_ID)
ORDER BY
RANK,
WRONG_ANS,
USER_ID