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
 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
   , 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
 left outer join
 (
  SELECT
   ENTRY_ID
   , PROBLEM_ID
   , SUBMITTED_AT
  FROM
   SUBMISSIONS
  WHERE
   STATUS <> 'AC'
   and ENTRY_ID is not null
   and CONTEST_ID = 2
 ) x
 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
),
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