with AC as (
select 
  USER_ID, 
  sum(POINT) AS POINT,
  max(SUBMITTED_AT) AS LAST_SUBMIT
from SUBMISSIONS
where STATUS = "AC"
and ENTRY_ID IS not null
and CONTEST_ID  = 2
group by USER_ID
), pena as (
       select 
  USER_ID, 
--   PROBLEM_ID,
  count(*) as WRONG_ANS
from SUBMISSIONS s1
where
ENTRY_ID IS not null
and CONTEST_ID  = 2
and STATUS != "AC"
and exists (
       select * from SUBMISSIONS s2
       where s1.USER_ID = s2.USER_ID
       and s1.PROBLEM_ID = s2.PROBLEM_ID
       and s2.STATUS = "AC"
       and s1.SUBMITTED_AT < s2.SUBMITTED_AT
)
group by USER_ID
-- , PROBLEM_ID
)
select 
RANK ( ) OVER ( order by a.POINT desc, strftime('%s', a.LAST_SUBMIT) - strftime('%s', e.STARTED_AT) + ifnull(p.WRONG_ANS, 0) * 300 )  AS RANK,

e.USER_ID, 
-- e.STARTED_AT,
a.POINT,
-- a.LAST_SUBMIT,
strftime('%s', a.LAST_SUBMIT) - strftime('%s', e.STARTED_AT) + ifnull(p.WRONG_ANS, 0) * 300 AS EX_TIME,
ifnull(p.WRONG_ANS, 0) AS WRONG_ANS
from ENTRIES e
left join AC a on a.USER_ID = e.USER_ID
left join pena p on  p.USER_ID = e.USER_ID
order by a.POINT desc, EX_TIME