WITH T1 AS (--得点
    SELECT
        USER_ID,
        SUM(POINT) AS POINT
    FROM
        SUBMISSIONS
    WHERE
        ENTRY_ID IS NOT NULL
        AND CONTEST_ID='2'
        AND STATUS='AC'
    GROUP BY
        USER_ID
),
T2 AS (--解答時間
    SELECT 
        E.USER_ID,
        STRFTIME('%s',LAST_AC)-STRFTIME('%s',STARTED_AT) AS TIME
    FROM
        ENTRIES AS E
        INNER JOIN (
            SELECT
                USER_ID,
                MAX(SUBMITTED_AT) AS LAST_AC
            FROM
                SUBMISSIONS
            WHERE
                ENTRY_ID IS NOT NULL
                AND CONTEST_ID='2'
                AND STATUS='AC'
            GROUP BY
                USER_ID
        ) AS S ON E.USER_ID=S.USER_ID
    GROUP BY
        E.USER_ID
),
T3 AS (--誤答数
    SELECT
        S.USER_ID,
        S.PROBLEM_ID,
        SUBMITTED_AT,
        LAST_AC,
        COUNT(SUBMIT_ID) AS WRONG_ANS
    FROM
        SUBMISSIONS AS S
        INNER JOIN (
            SELECT
                USER_ID,
                PROBLEM_ID,
                MAX(SUBMITTED_AT) AS LAST_AC
            FROM
                SUBMISSIONS
            WHERE
                ENTRY_ID IS NOT NULL
                AND CONTEST_ID='2'
                AND STATUS='AC'
            GROUP BY
                USER_ID,PROBLEM_ID
        ) AS S2 ON S.USER_ID=S2.USER_ID AND S.PROBLEM_ID=S2.PROBLEM_ID
    WHERE
        SUBMITTED_AT<LAST_AC
    GROUP BY
        S.USER_ID
    ORDER BY
        S.USER_ID,
        S.PROBLEM_ID
)
--最終的に抽出する部分
SELECT
    RANK() OVER (ORDER BY POINT DESC,TIME+IFNULL(WRONG_ANS,0)*300) AS RANK,
    T1.USER_ID,
    POINT,
    TIME+IFNULL(WRONG_ANS,0)*300 AS EX_TIME,
    IFNULL(WRONG_ANS,0) AS WRONG_ANS
FROM
    T1
    INNER JOIN T2 ON T1.USER_ID=T2.USER_ID
    LEFT JOIN T3 ON T1.USER_ID=T3.USER_ID
ORDER BY
    RANK,
    WRONG_ANS,
    T1.USER_ID