WITH temp_sub AS (
    SELECT
        contest_id
        , user_id
        , MAX(submitted_at) AS last_answer_at
        , SUM(point) AS total_points
    FROM
        submissions
    WHERE
        contest_id = 2
        AND entry_id IS NOT NULL
        AND status = 'AC'
    GROUP BY
        contest_id
        , user_id
), temp_sub_wr_count AS (
    SELECT
        contest_id
        , user_id
        , problem_id
        , status
        , submitted_at
        , MAX(CASE
            WHEN status = 'AC' THEN submitted_at
            ELSE NULL
        END) OVER(PARTITION BY contest_id, user_id, problem_id) AS problem_ac_at
    FROM
        submissions
    WHERE
        contest_id = 2
        AND entry_id IS NOT NULL
), sub_wr_count AS (
    SELECT
        contest_id
        , user_id
        , SUM(CASE
            WHEN (submitted_at < problem_ac_at OR problem_ac_at IS NULL) AND STATUS != 'AC' THEN 1
            ELSE 0
        END) AS wr_count
    FROM
        temp_sub_wr_count
    GROUP BY
        contest_id
        , user_id
)
SELECT
    RANK() OVER(ORDER BY total_points DESC, strftime('%s', a.last_answer_at) - strftime('%s', e.started_at) + 300 * b.wr_count) AS RANK
    , a.user_id AS USER_ID
    , a.total_points AS POINT
    , strftime('%s', a.last_answer_at) - strftime('%s', e.started_at) + 300 * b.wr_count AS EX_TIME
    , b.wr_count AS WRONG_ANS
FROM
    temp_sub a
    INNER JOIN
        sub_wr_count b
        ON a.contest_id = b.contest_id
            AND a.user_id = b.user_id
    INNER JOIN
        entries e
        ON a.contest_id = e.contest_id
            AND a.user_id = e.user_id
WHERE
    a.total_points > 0