WITH user_table AS(
    SELECT
        SUBMISSIONS.USER_ID
        , ENTRIES.STARTED_AT
        , SUBMISSIONS.SUBMITTED_AT
        , SUBMISSIONS.STATUS
        , SUBMISSIONS.POINT
    FROM
        SUBMISSIONS
        INNER JOIN 
        ENTRIES
        ON
        SUBMISSIONS.USER_ID = ENTRIES.USER_ID
    WHERE
        SUBMISSIONS.ENTRY_ID IS NOT NULL
        AND
        SUBMISSIONS.CONTEST_ID = '2'
)
, timediff AS(
    SELECT
        USER_ID
        , MAX(STRFTIME('%s', SUBMITTED_AT) - STRFTIME('%s', STARTED_AT)) AS timediff_max
        , MAX(SUBMITTED_AT) AS last_ac_time
        , SUM(POINT) AS point_sum
    FROM
        user_table
    WHERE
        STATUS = 'AC'
    GROUP BY
        1
    HAVING
        point_sum > 0
)
, error_count AS(
    SELECT
        user_table.USER_ID
        , COUNT(*) AS error_counts
    FROM
        user_table
        INNER JOIN
        timediff
        ON
        user_table.USER_ID = timediff.USER_ID
    WHERE
        SUBMITTED_AT < last_ac_time
        AND
        STATUS != 'AC'
    GROUP BY
        1
)
, scores AS (
    SELECT
        timediff.USER_ID
        , timediff_max + COALESCE(error_counts, 0) * 300 AS EX_TIME
        , point_sum AS POINT
        , COALESCE(error_counts, 0) AS WRONG_ANS
    FROM
        timediff
        LEFT OUTER JOIN
        error_count
        ON
        timediff.USER_ID = error_count.USER_ID
)
SELECT
    RANK() OVER(ORDER BY POINT DESC, EX_TIME) AS RANK
    , USER_ID
    , POINT
    , EX_TIME
    , WRONG_ANS
FROM
    scores
ORDER BY
    RANK
;