--  select a2.STARTED_AT, a1.SUBMITTED_AT  from SUBMISSIONS a1 left join ENTRIES a2 on a1.ENTRY_ID = a2.ENTRY_ID and a1.USER_ID = a2.USER_ID 
    --  where datetime(a2.STARTED_AT, '+1 hours') > a1.SUBMITTED_AT
select 
  RANK() OVER(ORDER BY RESULT DESC) RANK,
  USER_ID,
  POINT,
  EX_TIME,
  WRONG_ANS
from (
    select 
      cast(POINT || (9999 - EX_TIME) as integer) RESULT,
      USER_ID,
      POINT,
      EX_TIME,
      WRONG_ANS
    from (
        select 
           t1.USER_ID,
           SUM(t1.POINT) POINT,
           (select  strftime('%s', MAX(a1.SUBMITTED_AT))-strftime('%s', a2.STARTED_AT) from SUBMISSIONS a1 left join ENTRIES a2 on a1.ENTRY_ID = a2.ENTRY_ID and a1.USER_ID = a2.USER_ID where a1.USER_ID = t1.USER_ID and datetime(a2.STARTED_AT, '+1 hours') > a1.SUBMITTED_AT and a1.STATUS = 'AC'  group by a1.USER_ID) EX_TIME,
        --   ifnull((select COUNT(1) from SUBMISSIONS a1 left join ENTRIES a2 on a1.ENTRY_ID = a2.ENTRY_ID and a1.USER_ID = a2.USER_ID  where a1.USER_ID = t1.USER_ID and datetime(a2.STARTED_AT, '+1 hours') > a1.SUBMITTED_AT  and a1.STATUS = 'WA' group by a1.USER_ID), 0) WRONG_ANS
          ifnull((select COUNT(1) from SUBMISSIONS a1 left join ENTRIES a2 on a1.ENTRY_ID = a2.ENTRY_ID and a1.USER_ID = a2.USER_ID left join (SELECT w1.USER_ID, w1.ENTRY_ID, MAX(w1.PROBLEM_ID) MAX_PROBLEM_ID, MAX(w1.SUBMITTED_AT) MAX_SUBMITTED_AT FROM SUBMISSIONS w1 left join ENTRIES w2 on w1.ENTRY_ID = w2.ENTRY_ID and w1.USER_ID = w2.USER_ID WHERE w1.STATUS = 'AC' and datetime(w2.STARTED_AT, '+2 hours') > w1.SUBMITTED_AT GROUP BY w1.USER_ID) a3 on a1.USER_ID = a3.USER_ID where a1.USER_ID = t1.USER_ID and datetime(a2.STARTED_AT, '+2 hours') > a1.SUBMITTED_AT and a1.STATUS = 'WA' and a1.PROBLEM_ID >= a3.MAX_PROBLEM_ID and a1.SUBMITTED_AT <= a3.MAX_SUBMITTED_AT group by a1.USER_ID), 0) WRONG_ANS
        from SUBMISSIONS t1
          left join ENTRIES t2
            on t1.ENTRY_ID = t2.ENTRY_ID
            and t1.USER_ID = t2.USER_ID
        where
          t1.ENTRY_ID is not null
          and datetime(STARTED_AT, '+1 hours') > t1.SUBMITTED_AT
        group by
          t1.ENTRY_ID
          , t1.USER_ID
        order by 
          POINT desc
    ) x
    where 
      POINT <> 0
    order by 
      RESULT desc,
      WRONG_ANS,
      USER_ID
) x2
-- select * from SUBMISSIONS where USER_ID = '118'
  
  
-- select
-- ifnull( 
--     ( 
--         select
--             -- COUNT(1) 
--             a1.*
--             ,a3.MAX_PROBLEM_ID
--             ,a3.MAX_SUBMITTED_AT
--         from
--             SUBMISSIONS a1 
--             left join ENTRIES a2 
--                 on a1.ENTRY_ID = a2.ENTRY_ID 
--                 and a1.USER_ID = a2.USER_ID 
--             left join (
--                 SELECT 
--                   w1.USER_ID,
--                   w1.ENTRY_ID,
--                   MAX(w1.PROBLEM_ID) MAX_PROBLEM_ID,
--                   MAX(w1.SUBMITTED_AT) MAX_SUBMITTED_AT
--                 FROM
--                   SUBMISSIONS w1
--                 left join ENTRIES w2
--                     on w1.ENTRY_ID = w2.ENTRY_ID 
--                     and w1.USER_ID = w2.USER_ID 
--                 WHERE
--                   w1.STATUS = 'AC'
--                   and datetime(w2.STARTED_AT, '+2 hours') > w1.SUBMITTED_AT 
--                 GROUP BY
--                   w1.USER_ID
--             ) a3
--                 on a1.USER_ID = a3.USER_ID
--         where
--             a1.USER_ID = 64	
--             -- a1.USER_ID = t1.USER_ID 
--             and datetime(a2.STARTED_AT, '+2 hours') > a1.SUBMITTED_AT 
--             and a1.STATUS = 'WA' 
--             and a1.PROBLEM_ID >= a3.MAX_PROBLEM_ID
--             and a1.SUBMITTED_AT <= a3.MAX_SUBMITTED_AT
--         group by
--             a1.USER_ID
--     ) 
--     , 0
-- )