with c as (
    select
        e.USER_ID as USER_ID,
        sum(
            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 else 0 end
            else 0
            end
        ) as 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
),
c2 as (
    select
        e.USER_ID as USER_ID,
        sum(s.POINT) as POINT,
        (
            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 STRFTIME('%s', s.SUBMITTED_AT)
                else 0
                end
            ) - STRFTIME('%s', e.STARTED_AT)
        ) as EX_TIME
    from ENTRIES e
    inner join
        SUBMISSIONS s
        on s.ENTRY_ID = e.ENTRY_ID
    where
        e.CONTEST_ID = 2
    group by
        e.USER_ID
)
select
    rank() over (order by c2.POINT desc, max(c2.EX_TIME) + (300 * sum(c.WRONG_ANS))) as RANK,
    c.USER_ID as USER_ID,
    c2.POINT as POINT,
    max(c2.EX_TIME) + (300 * sum(c.WRONG_ANS)) as EX_TIME,
    sum(c.WRONG_ANS) as WRONG_ANS
from
    c
inner join
    c2
    on c2.USER_ID = c.USER_ID
group by c.USER_ID
having c2.POINT <> 0
order by
    RANK,
    WRONG_ANS,
    USER_ID;