-- ユーザーごとの開始日時、最終提出日時、点数
WITH AAA AS
(
    SELECT
        S.USER_ID,
        MAX(E.STARTED_AT) AS STARTED_AT,
        MAX(S.SUBMITTED_AT) AS SUBMITTED_AT,
        SUM(S.POINT) AS POINT
    FROM
        SUBMISSIONS AS S
        INNER JOIN
            ENTRIES AS E
            ON
                E.USER_ID = S.USER_ID
    WHERE
        S.CONTEST_ID=2
    AND S.ENTRY_ID IS NOT NULL
    AND S.STATUS='AC'
    
    GROUP BY
        S.USER_ID
    HAVING
        POINT > 0
),
-- ユーザーごとの誤答回数
CCC AS
(
    SELECT
        USER_ID,
        IFNULL(COUNT(*), 0) AS WA_CNT
    FROM
        SUBMISSIONS AS S
    WHERE
        S.CONTEST_ID=2
    AND S.ENTRY_ID IS NOT NULL
    AND S.STATUS='WA'
    AND
        EXISTS
        (
            SELECT
                *
            FROM
            (
                SELECT
                    S2.USER_ID,
                    S2.PROBLEM_ID,
                    MAX(S2.SUBMITTED_AT) AS SUBMITTED_AT
                FROM
                    SUBMISSIONS AS S2
                WHERE
                    S2.STATUS='AC'
                AND S2.ENTRY_ID IS NOT NULL
                GROUP BY
                    S2.USER_ID,
                    S2.PROBLEM_ID
            ) AS S2
            WHERE
                S2.USER_ID = S.USER_ID
            AND S2.PROBLEM_ID = S.PROBLEM_ID
            AND S2.SUBMITTED_AT > S.SUBMITTED_AT
        )
    GROUP BY
        USER_ID
)
SELECT
    RANK() OVER(ORDER BY AAA.POINT DESC, STRFTIME('%s', AAA.SUBMITTED_AT) - STRFTIME('%s', AAA.STARTED_AT) + (300 * IFNULL(CCC.WA_CNT, 0)) ASC) AS RANK,
    AAA.USER_ID AS USER_ID,
    AAA.POINT AS POINT,
    STRFTIME('%s', AAA.SUBMITTED_AT) - STRFTIME('%s', AAA.STARTED_AT) + (300 * IFNULL(CCC.WA_CNT, 0)) AS EX_TIME,
    IFNULL(CCC.WA_CNT, 0) AS WRONG_ANS
FROM
    AAA
    LEFT OUTER JOIN
        CCC
        ON
            CCC.USER_ID = AAA.USER_ID
ORDER BY
    RANK ASC,
    WRONG_ANS ASC,
    USER_ID ASC