SELECT 
    RANK() OVER (ORDER BY POINT DESC, EX_TIME ASC) AS RANK
    , USER_ID
    , POINT
    , EX_TIME
    , WRONG_ANS

FROM (
    SELECT
        t1.USER_ID
        , t1.POINT
        -- , t1.WRONG_ANS
        , COUNT(SUBMIT_ID) 
            FILTER (WHERE t1.STATUS != 'AC')
            OVER (PARTITION BY t1.USER_ID, t1.ENTRY_ID) AS WRONG_ANS
        , strftime('%s', datetime(t1.MAX_SUBMITTED_AT))
            - strftime('%s', datetime(t2.STARTED_AT))
            + (300 * 
                COUNT(SUBMIT_ID) 
                    FILTER (WHERE STATUS != 'AC')
                    OVER (PARTITION BY t1.USER_ID, t1.ENTRY_ID)
                ) AS EX_TIME
        
        
    FROM (
        SELECT
            USER_ID
            , SUBMIT_ID
            , ENTRY_ID
            , SUBMITTED_AT
            , STATUS
            , CONTEST_ID
            , SUM(POINT) OVER (PARTITION BY USER_ID, ENTRY_ID) AS POINT
            , MAX(SUBMITTED_AT) FILTER(WHERE STATUS = 'AC') OVER (PARTITION BY USER_ID, ENTRY_ID) AS MAX_SUBMITTED_AT
            -- , COUNT(SUBMIT_ID) 
            --     FILTER (WHERE STATUS != 'AC')
            --     OVER (PARTITION BY USER_ID, ENTRY_ID) AS WRONG_ANS
            -- COUNT (
            --     CASE 
            --         WHEN STATUS != 'AC'
            --             AND SUBMITTED_AT < MAX(SUBMITTED_AT) FILTER(WHERE STATUS = 'AC') OVER (PARTITION BY USER_ID, ENTRY_ID)SUBMIT_ID)
            --             THEN SUBMIT_ID
            --             ELSE NULL END
            --     ) AS WRONG_ANS
                        
        FROM SUBMISSIONS
        
        WHERE
            ENTRY_ID IS NOT NULL -- 回答時間外除外
            AND CONTEST_ID = 2
    ) AS t1
        INNER JOIN ENTRIES AS t2
        ON t1.USER_ID = t2.USER_ID
            AND t1.ENTRY_ID = t2.ENTRY_ID
            AND t1.CONTEST_ID = t2.CONTEST_ID
            
    WHERE
        t1.POINT > 0
        AND t1.MAX_SUBMITTED_AT >= t1.SUBMITTED_AT
        
    GROUP BY 1, 2
)

ORDER BY
    RANK ASC
    , WRONG_ANS ASC
    , USER_ID ASC