select RANK() over(order by b.POINT desc, b.EX_TIME) RANK
, b.USER_ID
, b.POINT
, b.EX_TIME
, b.WRONG_ANS
from (
select s1.USER_ID
, sum(S1.POINT) POINT 
, sum(case when s1.STATUS != 'AC' then 1 else 0 end) WRONG_ANS
, (strftime('%s', a.MAX_SUBMITTED_AT2) - strftime('%s', e.STARTED_AT))
  + (sum(case when s1.STATUS != 'AC' then 1 else 0 end) * 300)
 as EX_TIME
 , e.STARTED_AT
 , a.MAX_SUBMITTED_AT
 , a.MAX_SUBMITTED_AT2
from (
select  s0.USER_ID
, s0.ENTRY_ID, s0.CONTEST_ID, s0.PROBLEM_ID
, max(s0.SUBMITTED_AT) MAX_SUBMITTED_AT
, max(s0.SUBMITTED_AT) over(partition by s0.USER_ID) MAX_SUBMITTED_AT2
from SUBMISSIONS s0
where s0.CONTEST_ID = 2
and s0.STATUS = 'AC'
and s0.ENTRY_ID is not null
group by s0.USER_ID
, s0.ENTRY_ID, s0.CONTEST_ID, s0.PROBLEM_ID
) a
inner join ENTRIES e
  on e.ENTRY_ID = a.ENTRY_ID
  and e.CONTEST_ID = a.CONTEST_ID
  and e.USER_ID = a.USER_ID
inner join SUBMISSIONS s1
  on s1.USER_ID = a.USER_ID
  and s1.ENTRY_ID = a.ENTRY_ID
  and s1.CONTEST_ID = a.CONTEST_ID
  and s1.PROBLEM_ID = a.PROBLEM_ID
  and s1.SUBMITTED_AT <= a.MAX_SUBMITTED_AT
group by s1.USER_ID
having sum(S1.POINT) > 0
) b
order by RANK
, WRONG_ANS
, USER_ID
;