WITH entry_table as (SELECT
    *
    ,min(CASE WHEN status='AC' then submitted_at end) over (PARTITION BY user_id, problem_id) as latest_ac
    FROM (submissions s
    inner join entries e
    using (entry_id, contest_id, user_id))
    where e.entry_id is not NULL
    AND e.contest_id = 2
),
preprops as (SELECT
    user_id
    ,MAX(CASE WHEN status='AC' THEN strftime('%s', latest_ac) END) - MIN(strftime('%s',started_at)) as TIME_TO_ANSWER
    ,SUM(CASE WHEN status='WA' and  latest_ac > submitted_at THEN 1 else 0 END) as wrong_cnt
    ,SUM(point) as POINT
    from entry_table
    group by user_id
),
props as (SELECT
    user_id as USER_ID
    ,TIME_TO_ANSWER + wrong_cnt*300 as EX_TIME
    ,point as POINT
    ,wrong_cnt as WRONG_ANS
    from preprops
    where POINT > 0
)
SELECT
    RANK() OVER (ORDER BY POINT DESC, EX_TIME ASC) as RANK
    , USER_ID
    , POINT
    ,WRONG_ANS
    ,EX_TIME
FROM props
ORDER BY RANK, WRONG_ANS, USER_ID