WITH t1 AS (
    SELECT
        -- EN.ENTRY_ID
        EN.CONTEST_ID
        , EN.USER_ID
        , SUB.PROBLEM_ID
        , EN.STARTED_AT
        -- , SUB.SUBMIT_ID
        -- , SUB.CONTEST_ID
        -- , SUB.USER_ID
        -- , SUB.ENTRY_ID
        , SUB.SUBMITTED_AT
        , SUB.STATUS
        , SUB.POINT
    FROM
        ENTRIES AS EN
    INNER JOIN  -- コンテストに参加していない人(ENTRY_IDがnull)は採点から除外
        SUBMISSIONS AS SUB ON SUB.ENTRY_ID = EN.ENTRY_ID
    WHERE
        EN.CONTEST_ID = 2
        -- AND SUB.USER_ID = 118   -- DEBUG
)
, ANS_SEC AS (
    SELECT
        CONTEST_ID
        , USER_ID
        , strftime('%s', MAX(SUBMITTED_AT)) - strftime('%s', MIN(STARTED_AT)) AS SEC
    FROM
        t1
    WHERE
        STATUS = 'AC'
    GROUP BY
        CONTEST_ID
        , USER_ID
)
, WRONG_CNT AS (
    SELECT
        CONTEST_ID
        , USER_ID
        -- , PROBLEM_ID
        , COUNT(*) AS CNT
        -- , STATUS
    FROM
        t1
    WHERE
        STATUS != 'AC'  -- 'WA'や'RE'など'AC'でないものは複数存在する
    GROUP BY
        CONTEST_ID
        , USER_ID
        -- , PROBLEM_ID -- 特定のコンテストのユーザ別誤答数がわかればよい(問題別に集計する必要はない)
)
, TOTAL_POINT AS (
    SELECT
        CONTEST_ID
        , USER_ID
        , SUM(POINT) AS POINT
    FROM
        t1
    GROUP BY
        CONTEST_ID
        , USER_ID
)
SELECT
    RANK() OVER (
        -- PARTITION BY 
            -- ASEC.USER_ID
        ORDER BY 
            TP.POINT DESC
            , ASEC.SEC ASC
        ) AS RANK
    , ASEC.USER_ID
    , TP.POINT
    -- , ASEC.SEC
    , ASEC.SEC + (300 * WC.CNT) AS EX_TIME
    , WC.CNT AS WRONG_ANS
FROM
    -- TOTAL_POINT
    ANS_SEC AS ASEC
INNER JOIN
    WRONG_CNT AS WC
        ON WC.CONTEST_ID = ASEC.CONTEST_ID
        AND WC.USER_ID = ASEC.USER_ID
INNER JOIN
    TOTAL_POINT AS TP
        ON TP.CONTEST_ID = ASEC.CONTEST_ID
        AND TP.USER_ID = ASEC.USER_ID
ORDER BY
    RANK ASC
    , WRONG_ANS ASC
    , ASEC.USER_ID ASC
;