-- ユーザーごとの最終提出日時
WITH AAA AS
(
    SELECT
        USER_ID,
        MAX(SUBMITTED_AT) AS SUBMITTED_AT,
        SUM(POINT) AS POINT
    FROM
        SUBMISSIONS
    WHERE
        CONTEST_ID=2
    AND ENTRY_ID IS NOT NULL
    AND STATUS='AC'
    GROUP BY
        USER_ID
),
-- ユーザーごとの開始日時
BBB AS
(
    SELECT
        USER_ID,
        MIN(STARTED_AT) AS STARTED_AT
    FROM
        ENTRIES
    WHERE
        CONTEST_ID=2
    GROUP BY
        USER_ID
),
-- ユーザーごとの誤答回数
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 S.SUBMITTED_AT < (SELECT MAX(SUBMITTED_AT) FROM SUBMISSIONS WHERE CONTEST_ID=2 AND ENTRY_ID IS NOT NULL AND STATUS='AC' AND USER_ID=S.USER_ID)
    GROUP BY
        USER_ID
)

SELECT
    RANK() OVER(ORDER BY AAA.POINT DESC, STRFTIME('%s', AAA.SUBMITTED_AT) - STRFTIME('%s', BBB.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', BBB.STARTED_AT) + (300 * IFNULL(CCC.WA_CNT, 0)) AS EX_TIME,
    IFNULL(CCC.WA_CNT, 0) AS WRONG_ANS
FROM
    AAA
    LEFT OUTER JOIN
        BBB
        ON
            BBB.USER_ID = AAA.USER_ID
    LEFT OUTER JOIN
        CCC
        ON
            CCC.USER_ID = AAA.USER_ID
ORDER BY
    RANK ASC,
    WRONG_ANS ASC,
    USER_ID ASC