with 
t as (
  select s.USER_ID, PROBLEM_ID, SUBMITTED_AT, STATUS, STARTED_AT, POINT
  from SUBMISSIONS as s
  INNER JOIN ENTRIES USING(USER_ID, CONTEST_ID)
  WHERE CONTEST_ID = 2
),
up_ac as (
  select USER_ID, PROBLEM_ID, SUBMITTED_AT, STARTED_AT
  from t
  where STATUS = 'AC'
),
up_nonac as (
  select up_ac.USER_ID, up_ac.PROBLEM_ID, count(distinct t.SUBMITTED_AT) as non_acs
  from t
  inner join up_ac using(USER_ID, PROBLEM_ID)
  where STATUS != 'AC' and t.SUBMITTED_AT < up_ac.SUBMITTED_AT
  group by USER_ID, PROBLEM_ID
),
u_nonac as (
  select USER_ID, sum(non_acs) as non_acs
  from up_nonac
  group by USER_ID
),
u_points as (
  select USER_ID, sum(POINT) as points, max(SUBMITTED_AT) as lastsub, min(STARTED_AT) as start_at
  from t
  WHERE STATUS = 'AC'
  GROUP BY USER_ID
),
j as (
  select USER_ID, points, lastsub, start_at, IFNULL(non_acs, 0) as non_ac
  from u_points left join u_nonac using(USER_ID)
  where points > 0
), 
calc as (
  select *, unixepoch(datetime(lastsub)) - unixepoch(datetime(start_at)) + (300 * non_ac) as solved_sec
  from j
)
select
  rank() over (order by points desc, solved_sec) as RANK,
  USER_ID,
  points as POINT,
  solved_sec as EX_TIME,
  non_ac as WRONG_ANS
from calc
order by RANK, WRONG_ANS, USER_ID