select 
  RANK() OVER(ORDER BY cast(c1.POINT || (9999 - c1.EX_TIME) as integer) DESC) RANK,
  c1.USER_ID,
  c1.POINT,
  c1.EX_TIME,
  c1.WRONG_ANS
from (
    select
      b1.USER_ID,
      SUM(b1.POINT) POINT,
      EX_TIME,
      WRONG_ANS
    from (
        select
          a1.*,
          strftime('%s', a3.MAX_SUBMITTED_AT) - strftime('%s', a2.STARTED_AT)  EX_TIME,
          a4.COUNT_WA WRONG_ANS
        from 
          SUBMISSIONS a1
          inner join ENTRIES a2
            on a1.ENTRY_ID = a2.ENTRY_ID
            and a1.CONTEST_ID = a2.CONTEST_ID
            and a1.USER_ID = a2.USER_ID
          -- EX_TIME用
          left join (
            SELECT
              ENTRY_ID,
              CONTEST_ID,
              USER_ID,
              MAX(SUBMITTED_AT) MAX_SUBMITTED_AT
            FROM
              SUBMISSIONS
            WHERE
              STATUS = 'AC'
              and ENTRY_ID is not null
            GROUP BY
              ENTRY_ID,
              CONTEST_ID,
              USER_ID
          ) a3
            on a3.ENTRY_ID = a1.ENTRY_ID
            and a3.CONTEST_ID = a1.CONTEST_ID
            and a3.USER_ID = a1.USER_ID
          -- WRONG_ANS用
          left join (
            select 
              PROBLEM_ID,
              USER_ID,
              COUNT(WA) COUNT_WA
            from (
              select
                *
              from SUBMISSIONS w1
              left join (
                select
                  PROBLEM_ID,
                  USER_ID,
                  SUBMITTED_AT,
                  'WA' WA
                from SUBMISSIONS 
                where
                  STATUS = 'WA'
                  and ENTRY_ID is not null
              ) w2
              on w1.PROBLEM_ID = w2.PROBLEM_ID
              and w1.USER_ID = w2.USER_ID
              and w1.SUBMITTED_AT > w2.SUBMITTED_AT
              where
                w1.STATUS = 'AC'
                and w1.ENTRY_ID is not null
            )
            group by
              USER_ID
          ) a4
            on a4.USER_ID = a1.USER_ID
        where
          datetime(a2.STARTED_AT, '+1 hours') > a1.SUBMITTED_AT
    ) b1
    group by
      b1.USER_ID
) c1
where
  POINT <> 0
order by
  RANK,
  WRONG_ANS,
  USER_ID
;


-- select * from SUBMISSIONS where USER_ID = '118'