WITH
_preTime AS(
SELECT
    *,
    CASE WHEN SUBMITTED_AT  < MAX(ACCEPT_AT) OVER(PARTITION BY USER_ID, PROBLEM_ID) THEN 1 ELSE 0 END  AS mistake_flag
FROM(
    SELECT
        USER_ID,
        PROBLEM_ID,
        SUBMIT_ID,
        SUBMITTED_AT,
        ENTRY_ID,
        CASE WHEN STATUS = "AC" THEN SUBMIT_ID ELSE NULL END AS ACCEPT_ID,
        CASE WHEN point > 0 THEN SUBMITTED_AT ELSE NULL END AS ACCEPT_AT,
        STATUS,
        point
    FROM
        SUBMISSIONS
    WHERE TRUE
        AND CONTEST_ID = 2
        AND ENTRY_ID IS NOT NULL
    )
),
_mistake AS(
SELECT
    USER_ID,
    SUM(mistake_flag) AS mistakes
FROM
    _preTime
GROUP BY
    user_id
),

_time AS(
SELECT
    USER_ID,
    SUM(POINT) AS POINT,
    STRFTIME('%s',MAX(SUBMITTED_AT)) -  STRFTIME('%s', STARTED_AT) AS CLEAR_TIME
FROM
    SUBMISSIONS
INNER JOIN ENTRIES USING(USER_ID)
WHERE TRUE
    AND STATUS = "AC"
    AND SUBMISSIONS.CONTEST_ID = 2
    AND SUBMISSIONS.ENTRY_ID IS NOT NULL
GROUP BY
    USER_ID,
    STARTED_AT
)


SELECT
    RANK() OVER(ORDER BY point DESC, EX_TIME) AS RANK,
    USER_ID,
    POINT,
    EX_TIME,
    mistakes AS WRONG_ANS
FROM(
    SELECT
        *,
        CLEAR_TIME + (mistakes * 300) AS EX_TIME
    FROM
        _time
    INNER JOIN  _mistake USING(USER_ID)
)
ORDER BY
    RANK,
    WRONG_ANS,
    USER_ID