WITH entry_table as (SELECT
    *
    ,min(CASE WHEN status='AC' then submitted_at else NULL end) over (PARTITION BY user_id, problem_id) as latest_ac
    FROM (entries e
    inner join submissions s
    using (entry_id, contest_id, user_id))
    where e.entry_id is not NULL
    AND e.started_at < s.submitted_at
    AND e.contest_id = 2
),
preprops as (SELECT
    user_id
    ,SUM(CASE WHEN status='AC' THEN strftime('%s', latest_ac) - strftime('%s',started_at) ELSE 0 END) 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, WRONG_ANS ASC) as RANK
    , USER_ID
    , POINT
    ,WRONG_ANS
    ,EX_TIME
FROM props
ORDER BY RANK, WRONG_ANS, USER_ID