SELECT RANK() OVER (ORDER BY SCORE DESC ,EXTIME) AS RANK ,USER_ID,SCORE as POINT,EXTIME AS EX_TIME,WACNT AS WRONG_ANS
FROM (
SELECT USER_ID,SUM(POINT) AS SCORE,
SUM(WACNT*300)+SUM(TIME) AS EXTIME,SUM(WACNT) AS WACNT
 FROM (
SELECT USER_ID,PROBLEM_ID,POINT,WACNT,
strftime('%s',SUBMITTED_AT)-strftime('%s',LAG(SUBMITTED_AT,1,STARTED_AT) OVER (PARTITION BY USER_ID ORDER BY USER_ID,SUBMITTED_AT)) as time
FROM (
SELECT E.USER_ID,AC.PROBLEM_ID,AC.SUBMITTED_AT,E.STARTED_AT,AC.POINT,COUNT(WA.SUBMITTED_AT) AS WACNT
FROM ENTRIES E
 INNER JOIN (
 --AC対象
   SELECT PROBLEM_ID,USER_ID,SUBMITTED_AT,STATUS,POINT
   FROM SUBMISSIONS
   WHERE CONTEST_ID = 2
   AND STATUS = 'AC'
   AND ENTRY_ID IS NOT NULL
 )AC ON (AC.USER_ID = E.USER_ID)
 LEFT OUTER JOIN (
 --WA対象
   SELECT PROBLEM_ID,USER_ID,SUBMIT_ID,SUBMITTED_AT
   FROM SUBMISSIONS
   WHERE CONTEST_ID = 2
   AND STATUS = 'WA'
   AND ENTRY_ID IS NOT NULL
 )WA ON (AC.USER_ID = WA.USER_ID AND AC.PROBLEM_ID = WA.PROBLEM_ID AND WA.SUBMITTED_AT < AC.SUBMITTED_AT)

WHERE E.CONTEST_ID = 2
GROUP BY  E.USER_ID,AC.PROBLEM_ID,AC.SUBMITTED_AT,E.STARTED_AT,AC.POINT
ORDER BY E.USER_ID,AC.SUBMITTED_AT
)
ORDER BY USER_ID,SUBMITTED_AT

)
GROUP BY USER_ID
ORDER BY USER_ID
)