--エントリID、問題IDごとの正解
WITH T1 AS (
SELECT  S.ENTRY_ID
       ,S.PROBLEM_ID
       ,S.SUBMITTED_AT AS AC_SUBMITTED_AT
  FROM SUBMISSIONS AS S
 WHERE S.ENTRY_ID IS NOT NULL
   AND S.STATUS = 'AC'
),
--誤答数のカウント
WA AS (
SELECT  S.ENTRY_ID
       ,COUNT(*) AS CNT_WA
  FROM SUBMISSIONS AS S
 INNER JOIN T1 AS T
    ON S.ENTRY_ID = T.ENTRY_ID
   AND S.PROBLEM_ID = T.PROBLEM_ID
   AND S.SUBMITTED_AT <= T.AC_SUBMITTED_AT
 WHERE S.STATUS = 'WA'
 GROUP BY S.ENTRY_ID
),
-- ユーザIDごとの得点集計、経過時間計算、誤答数
T4 AS (
SELECT  E.USER_ID AS USER_ID
       ,SUM(S.POINT) AS POINT
       ,STRFTIME('%s',  MAX(S.SUBMITTED_AT)) - STRFTIME('%s', E.STARTED_AT) +
        IFNULL(W.CNT_WA, 0) * 5 * 60 AS EX_TIME
       ,IFNULL(W.CNT_WA, 0) AS WRONG_ANS
  FROM SUBMISSIONS AS S
 INNER JOIN ENTRIES AS E
    ON S.ENTRY_ID = E.ENTRY_ID
  LEFT OUTER JOIN WA AS W
    ON S.ENTRY_ID = W.ENTRY_ID
 WHERE S.ENTRY_ID IS NOT NULL
 GROUP BY  E.USER_ID
          ,WRONG_ANS
HAVING SUM(S.POINT) <> 0
)
SELECT  RANK() OVER (ORDER BY POINT DESC, EX_TIME DESC, WRONG_ANS DESC) AS RANK
       ,POINT
       ,EX_TIME
       ,WRONG_ANS
  FROM T4
 ORDER BY  RANK
          ,WRONG_ANS 
          ,USER_ID