with v1 as
(
    select
        ENT.USER_ID as USER_ID
    ,   SUB.POINT as SCORE
    ,   SUB.PROBLEM_ID as PROB
    ,   STATUS as STATUS
    ,   strftime('%s', ENT.STARTED_AT) as START
    ,   strftime('%s', SUB.SUBMITTED_AT) as SUBMIT
    from
        SUBMISSIONS SUB
    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
)
select
    RANK() OVER(ORDER BY sum(v1.SCORE) DESC, max(AC.SUBMIT) - AC.START + sum(v1.SUBMIT<AC.SUBMIT) * 300) as RANK
,   v1.USER_ID as USER_ID
,   sum(v1.SCORE) as POINT
,   max(AC.SUBMIT) - AC.START + sum(v1.SUBMIT<AC.SUBMIT) * 300 as EX_TIME
,   sum(v1.SUBMIT<AC.SUBMIT) as WRONG_ANS
from
    v1
join
    ( select * from v1 where v1.STATUS = 'AC' ) as AC
on
    v1.USER_ID = AC.USER_ID
and v1.PROB = AC.PROB
group by
    v1.USER_ID
having
    POINT > 0
order by
    RANK
,   WRONG_ANS
,   USER_ID