with c as (
    select
        e.USER_ID as c_USER_ID,
        sum(s.POINT) as c_POINT,
        round((max(case when s.SUBMITTED_AT = (
            select max(s2.SUBMITTED_AT)
            from SUBMISSIONS s2
            where s2.ENTRY_ID = s.ENTRY_ID and s2.STATUS = 'AC' 
            )
        then julianday(s.SUBMITTED_AT)
        end) - julianday(e.STARTED_AT)) * 24 * 60 * 60) as c_EX_TIME,
        count(
            case when s.SUBMITTED_AT < (
                select max(s2.SUBMITTED_AT)
                from SUBMISSIONS s2
                where s2.ENTRY_ID = s.ENTRY_ID and s2.PROBLEM_ID = s.PROBLEM_ID and s2.STATUS = 'AC' 
            )
            then 
                case when s.STATUS <> 'AC' then 1 end
            end
        ) as c_WRONG_ANS
    from ENTRIES e
    inner join
        SUBMISSIONS s
        on s.ENTRY_ID = e.ENTRY_ID
    where
        e.CONTEST_ID = 2
    group by
        e.USER_ID,
        s.PROBLEM_ID
)
select
    rank() over (order by sum(c_POINT) desc, max(c_EX_TIME) + 300 * c_WRONG_ANS) as RANK,
    c_USER_ID as USER_ID,
    sum(c_POINT) as POINT,
    max(c_EX_TIME) + 300 * c_WRONG_ANS as EX_TIME,
    sum(c_WRONG_ANS) as WRONG_ANS
from c
group by USER_ID
having c_POINT <> 0
order by
    RANK,
    WRONG_ANS,
    USER_ID;