with vAC as
(select
     s.USER_ID
    ,s.SUBMITTED_AT
    ,rank() over(partition by USER_ID order by s.SUBMITTED_AT desc) rnk
    from SUBMISSIONS s
    where s.STATUS = 'AC'
    and s.ENTRY_ID is not null
    and s.CONTEST_ID=2)
, vNotAC as
(select
     s.USER_ID
    ,count(*) CNT_NOT_AC
    from SUBMISSIONS s
    where ifnull(s.STATUS,' ') <> 'AC'
    and s.CONTEST_ID=2
    group by s.USER_ID)
, vSUB as
(select
     s.USER_ID
    ,s.ENTRY_ID
    ,min(s.SUBMITTED_AT) SUBMITTED_AT
    ,sum(s.POINT) POINT
    from SUBMISSIONS s
    where s.CONTEST_ID=2
    and s.ENTRY_ID is not null
    group by s.USER_ID,s.ENTRY_ID
    having sum(s.POINT)<>0)
select *
from (select
 rank() over(order by s.POINT desc,(strftime('%s',a.SUBMITTED_AT) - strftime('%s',e.STARTED_AT)) + (300 * ifnull(na.CNT_NOT_AC,0))) RANK
,s.USER_ID USER_ID
--,s.SUBMITTED_AT time_start
--,a.SUBMITTED_AT time_end
--,strftime('%s',a.SUBMITTED_AT) - strftime('%s',s.SUBMITTED_AT)
,s.POINT
,(strftime('%s',a.SUBMITTED_AT) - strftime('%s',e.STARTED_AT)) + (300 * ifnull(na.CNT_NOT_AC,0)) EX_TIME
,ifnull(na.CNT_NOT_AC,0) WRONG_ANS
from vSUB s
inner join ENTRIES e
on s.USER_ID = e.USER_ID
and s.ENTRY_ID = e.ENTRY_ID
inner join vAC a
on s.USER_ID = a.USER_ID
and a.rnk = 1
left join vNotAC na
on s.USER_ID = na.USER_ID) a
order by 
a.RANK