WITH KAITOTIMETABLE AS
(
--実解答時間
SELECT
 x.ENTRY_ID
 , strftime('%s', y.ACmax) - strftime('%s', x.STARTED_AT) as kaitotime
FROM
 ENTRIES x
 inner join
 (
  SELECT
   ENTRY_ID
   , MAX(SUBMITTED_AT) as ACmax
  FROM
   SUBMISSIONS
  WHERE
   STATUS ='AC'
   and ENTRY_ID is not null
  GROUP BY
   ENTRY_ID
 ) y
 on x.ENTRY_ID = y.ENTRY_ID
WHERE
 x.CONTEST_ID = 2
) ,
NGCOUNTTABLE AS
(
--問題別誤答数
SELECT
 ENTRIES.ENTRY_ID as ENTRY_ID
 , (CASE WHEN ngcount is not null THEN ngcount ELSE 0 END) as ngcount
FROM
 ENTRIES
 left outer join
 (
  ---誤答数がある人のみ
  SELECT
   a.ENTRY_ID
    , COUNT(a.ENTRY_ID) as ngcount
  FROM
  (
   SELECT
     x.ENTRY_ID
      , x.PROBLEM_ID
      , x.SUBMITTED_AT
   FROM
   (
    SELECT
     ENTRY_ID
     , PROBLEM_ID
     , SUBMITTED_AT
    FROM
     SUBMISSIONS
    WHERE
     STATUS <> 'AC'
     and ENTRY_ID is not null
     and CONTEST_ID = 2
   ) x
   left outer join
   (
    SELECT
     ENTRY_ID
     , PROBLEM_ID
     , MAX(SUBMITTED_AT) as ACmax
    FROM
     SUBMISSIONS
    WHERE
     STATUS ='AC'
     and ENTRY_ID is not null
     and CONTEST_ID = 2
    GROUP BY
     ENTRY_ID
     , PROBLEM_ID
   ) y
   on x.ENTRY_ID = y.ENTRY_ID
   and x.PROBLEM_ID = y.PROBLEM_ID
   WHERE
    x.SUBMITTED_AT < y.ACmax
  ) a
  GROUP BY
   a.ENTRY_ID
 ) aa
 on ENTRIES.ENTRY_ID = aa.ENTRY_ID
WHERE
 ENTRIES.CONTEST_ID = 2
),
SOUTOKUTENTABLE AS
(
SELECT
 ENTRY_ID
 , USER_ID
 , SUM(POINT) as soutokuten
FROM
 SUBMISSIONS
WHERE
 ENTRY_ID is not null
 and CONTEST_ID = 2
GROUP BY
 ENTRY_ID
 , USER_ID
HAVING
 soutokuten > 0
)
SELECT
 RANK() OVER (
  ORDER BY
   POINT desc
   , EX_TIME
 ) AS RANK
 , USER_ID
 , POINT
 , EX_TIME
 , WRONG_ANS
FROM
(
SELECT
 t3.USER_ID as USER_ID
 , t3.soutokuten as POINT
 , (t1.kaitotime + 300*t2.ngcount) as EX_TIME
 , t2.ngcount as WRONG_ANS
FROM
 SOUTOKUTENTABLE t3
 inner join
 KAITOTIMETABLE t1
 on t3.ENTRY_ID = t1.ENTRY_ID
 inner join
 NGCOUNTTABLE t2
 on t3.ENTRY_ID = t2.ENTRY_ID
) z
ORDER BY
 RANK
 , WRONG_ANS
 , USER_ID