WITH
ac_base AS (
    SELECT
        *
    FROM
        SUBMISSIONS
    WHERE
        CONTEST_ID = 2
        AND ENTRY_ID IS NOT NULL
        AND  STATUS = 'AC'
)
, valid_wa_base AS (
    SELECT
        SUBMISSIONS.*
    FROM
        SUBMISSIONS
    INNER JOIN
        ac_base
    ON
        SUBMISSIONS.USER_ID = ac_base.USER_ID
        AND SUBMISSIONS.PROBLEM_ID = ac_base.PROBLEM_ID
        AND SUBMISSIONS.SUBMITTED_AT < ac_base.SUBMITTED_AT
    WHERE
        SUBMISSIONS.CONTEST_ID = 2
        AND SUBMISSIONS.ENTRY_ID IS NOT NULL
        AND SUBMISSIONS.STATUS = 'WA'
)
, point_tbl AS (
    SELECT
        USER_ID
        , SUM(POINT) AS POINT
    FROM
        ac_base
    GROUP BY
        USER_ID
)
, ex_time_tbl AS (
    SELECT
        USER_ID
        , STRFTIME('%s', MAX(SUBMITTED_AT)) - STRFTIME('%s', MIN(STARTED_AT)) AS EX_TIME
    FROM
        ac_base
    LEFT JOIN
        ENTRIES
    USING (ENTRY_ID, CONTEST_ID, USER_ID)
    GROUP BY
        USER_ID
)
, wrong_ans_tbl AS (
    SELECT
        USER_ID
        , COUNT(PROBLEM_ID) AS WRONG_ANS
    FROM
        valid_wa_base
    GROUP BY
        USER_iD
)
, base AS (
    SELECT
        USER_ID
        , POINT
        , EX_TIME + COALESCE(WRONG_ANS, 0) * 300 AS EX_TIME
        , COALESCE(WRONG_ANS, 0) AS WRONG_ANS
    FROM
        point_tbl
    LEFT JOIN
        ex_time_tbl
    USING(USER_ID)
    LEFT JOIN
        wrong_ans_tbl
    USING(USER_ID)
)
SELECT
    RANK() OVER (ORDER BY POINT DESC, EX_TIME ASC) AS RANK
    , USER_ID
    , POINT
    , EX_TIME
    , WRONG_ANS
FROM
    base
ORDER BY
    RANK ASC
    , WRONG_ANS ASC
    , USER_ID ASC