with v1 as
(
    select
        ENT.USER_ID as USER_ID
    ,   SUB.POINT as POINT
    ,   SUB.PROBLEM_ID as PROB
    ,   SUB.STATUS as STATUS
    ,   strftime('%s', ENT.STARTED_AT) as START
    ,   strftime('%s', SUB.SUBMITTED_AT) as SUBMIT
    from
        SUBMISSIONS SUB
    inner join
        ENTRIES ENT
    on
        ENT.ENTRY_ID = SUB.ENTRY_ID
    and ENT.CONTEST_ID = SUB.CONTEST_ID
    and ENT.USER_ID = SUB.USER_ID
    where
        SUB.CONTEST_ID = '2'
    and ENT.ENTRY_ID is not NULL
)
, AC as
(
    select * from v1 where v1.STATUS = 'AC'
)
select
    RANK() OVER(ORDER BY t1.POINT DESC, t1.EX_TIME) as RANK
,   t1.USER_ID
,   t1.POINT
,   t1.EX_TIME
,   t1.WRONG_ANS
from
(
    select
        AC.USER_ID as USER_ID
    ,   sum(AC.POINT) as POINT
    ,   max(AC.SUBMIT) - AC.START + coalesce(t2.CNT,0)*300 as EX_TIME
    ,   coalesce(t2.CNT,0) as WRONG_ANS
    from
        AC
    left outer join
        (
            select
                v1.USER_ID as USER_ID
            ,   count(*) as CNT
            from
                v1
            inner join
                AC
            on
                v1.USER_ID = AC.USER_ID
            and v1.PROB = AC.PROB
            where
                v1.SUBMIT < AC.SUBMIT
            group by
                v1.USER_ID
        ) t2
    on
        AC.USER_ID = t2.USER_ID
    where
        POINT > 0
    group by
        AC.USER_ID
) t1
order by
    RANK
,   WRONG_ANS
,   USER_ID