SELECT 
RANK() OVER(ORDER BY SUM(点数) DESC,MAX(EX_TIME)+SUM(誤りフラグ)*300 ASC) RANK
,ユーザーID USER_ID
--,SUM(誤りフラグ)
,SUM(点数) POINT
,MAX(EX_TIME)+SUM(誤りフラグ)*300 EX_TIME
,SUM(誤りフラグ) WRONG_ANS
FROM (
    SELECT
        A.SUBMIT_ID       提出ID
        ,A.CONTEST_ID      コンテストID
        ,A.PROBLEM_ID      問題ID
        ,A.USER_ID         ユーザーID
        ,A.ENTRY_ID        エントリーID
        ,B.STARTED_AT       開始日時
        ,A.SUBMITTED_AT    提出日時
        ,A.STATUS          ステータス
        ,A.POINT           点数
        ,case WHEN A.POINT =0  THEN 1 ELSE 0 END  誤りフラグ
        ,B.ENTRY_ID        エントリーID
        ,CASE WHEN A.STATUS ='AC' THEN strftime('%s', D.ALL_LAST_SUBMITTED_AT) - strftime('%s', B.STARTED_AT)  ELSE 0 END EX_TIME
    FROM
        SUBMISSIONS A
    INNER JOIN
        ENTRIES B
        ON
            A.ENTRY_ID = B.ENTRY_ID

    LEFT OUTER JOIN
        (
            SELECT USER_ID,PROBLEM_ID,MAX(SUBMITTED_AT) PROBLEM_SUBMITTED_AT
            FROM SUBMISSIONS
            WHERE CONTEST_ID ='2'
            AND STATUS='AC'
            AND ENTRY_ID IS NOT NULL
            GROUP BY USER_ID,PROBLEM_ID
        ) C
        ON 
             A.USER_ID = C.USER_ID
             AND A.PROBLEM_ID = C.PROBLEM_ID
    LEFT OUTER JOIN
        (
            SELECT USER_ID,MAX(SUBMITTED_AT) ALL_LAST_SUBMITTED_AT
            FROM SUBMISSIONS
            WHERE CONTEST_ID ='2'
            AND STATUS='AC'
            AND ENTRY_ID IS NOT NULL
            GROUP BY USER_ID
        ) D
        ON 
             A.USER_ID = D.USER_ID
    WHERE
        A.CONTEST_ID ='2'
        AND A.ENTRY_ID IS NOT NULL
        AND C.PROBLEM_SUBMITTED_AT >= A.SUBMITTED_AT
)
GROUP BY ユーザーID
order BY RANK