/*SELECT 順位(1から順にカウントアップ) → RANK
ユーザID → USER_ID
ユーザが獲得した点数 → POINT
問題提出までにかかった合計時間(ペナルティを含む) → EX_TIME
誤答数 → WRONG_ANS
FROM
WHERE 対象となるコンテストは、CONTEST_ID = 2 とする。
合計した点数が０点のデータは集計対象外とする。
GROUP BY
ORDER BY 順位の昇順
誤答数の昇順
ユーザIDの昇順*/

--ACの前の回答数
SELECT USER_ID
       ,ENTRY_ID 
       ,STATUS
       ,POINT
       ,SUBMIT_ID
  FROM SUBMISSIONS
SELECT RANK() OVER(PARTITION BY SUM_TIME.USER_ID ORDER BY SUM_TIME.POINT asc, ANSWER_TIME.EX_TIME desc)  AS 'RANK'
       ,SUM_TIME.USER_ID AS USER_ID
       ,SUM_TIME.POINT AS POINT
       ,ANSWER_TIME.EX_TIME AS EX_TIME
       ,ifnull(MISS_ANSWER.WRONG_ANS,0) AS WRONG_ANS
--合計点数
FROM (SELECT USER_ID
             ,ENTRY_ID
      ,SUM(POINT) AS POINT
FROM SUBMISSIONS
WHERE 1=1
AND ENTRY_ID  IS NOT NULL
AND STATUS='AC'
AND CONTEST_ID=2
GROUP BY USER_ID) AS SUM_TIME
LEFT JOIN
--解答時間
(SELECT SUB.USER_ID AS USER_ID
       ,strftime('%s',SUBMITTED_AT)-strftime('%s',STARTED_AT) AS EX_TIME
  FROM SUBMISSIONS AS SUB
LEFT JOIN ENTRIES AS ENTRY
ON SUB.ENTRY_ID=SUB.ENTRY_ID 
WHERE 1=1
AND SUB.ENTRY_ID IS NOT NULL
AND SUB.CONTEST_ID=2) AS ANSWER_TIME

ON SUM_TIME.USER_ID=ANSWER_TIME.USER_ID

/*残りの時間の条件が、ACより早くAC以外の回答をしたら、誤答のペナルティ*/
/*INNER JOIN 
(SELECT USER_ID AS USER_ID
       ,CASE WHEN 
FROM ANSWER_TIME
LEFT JOIN
ON
WHERE)
ON */

--誤答数
LEFT JOIN
(SELECT USER_ID AS USER_ID
        ,COUNT(STATUS) AS WRONG_ANS
   FROM SUBMISSIONS
   WHERE 1=1
   AND ENTRY_ID  IS NOT NULL
   AND STATUS<>'AC'
   
   AND CONTEST_ID=2
  GROUP BY USER_ID ) AS MISS_ANSWER
ON SUM_TIME.USER_ID=MISS_ANSWER.USER_ID

WHERE 1=1
AND SUM_TIME.POINT<>0

GROUP BY SUM_TIME.USER_ID 
       ,ANSWER_TIME.EX_TIME 
       ,SUM_TIME.POINT
       ,MISS_ANSWER.WRONG_ANS 
ORDER BY  RANK
         ,WRONG_ANS
         ,USER_ID