WITH
_entry AS(
SELECT 
    ENTRY_ID,
    USER_ID,
    STARTED_AT
FROM
    ENTRIES
WHERE
    CONTEST_ID = 2
),


_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,
    MAX(CLEAR_TIME) AS CLEAR_TIME
FROM(
    SELECT
        _preTime.USER_ID AS USER_ID,
        _preTime.PROBLEM_ID AS PROBLEM_ID,
        _preTime.ENTRY_ID AS ENTRY_ID,
        point,
        STRFTIME('%s', ACCEPT_AT) - STRFTIME('%s', STARTED_AT) AS CLEAR_TIME
    FROM
        _preTime
    INNER JOIN _entry USING(USER_ID, ENTRY_ID)
    WHERE
        ACCEPT_AT IS NOT NULL
)
GROUP BY
    USER_ID
)

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)
)