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