WITH SCORE AS(
SELECT
USER_ID,
SUM(POINT) AS SCORE
FROM SUBMISSIONS
WHERE CONTEST_ID = 2 
AND ENTRY_ID IS NOT NULL
GROUP BY USER_ID
),
ACTIME AS(
SElECT 
PROBLEM_ID,
USER_ID,
SUBMITTED_AT AS AC_TIME
FROM SUBMISSIONS
WHERE CONTEST_ID = 2 
AND STATUS ="AC"
AND ENTRY_ID IS NOT NULL
),
LASTACTIME AS(
SELECT
USER_ID,
MAX(datetime(AC_TIME)) AS LAST_AC
FROM ACTIME
GROUP BY USER_ID
),
CNTWA AS(
SELECT
S.PROBLEM_ID,
S.USER_ID,
SUM(
CASE WHEN AC.AC_TIME IS NOT NULL AND datetime(SUBMITTED_AT) < datetime(AC_TIME) THEN 1
ELSE 0 END
) AS CNT_WA
FROM SUBMISSIONS S LEFT JOIN ACTIME AC ON AC.PROBLEM_ID = S.PROBLEM_ID AND AC.USER_ID = S.USER_ID
WHERE CONTEST_ID = 2 AND STATUS="WA"
GROUP BY 1,2
),
CNTWAALL AS(
SELECT 
USER_ID,
SUM(CNT_WA) AS WA
FROM CNTWA
GROUP BY 1
),
RANKING AS(
SELECT 
S.USER_ID,
S.SCORE AS POINT,
strftime('%s', LAST_AC) - strftime('%s',STARTED_AT) AS TIME,
--LAST_AC,
--STARTED_AT,
CASE
WHEN WA IS NULL THEN 0
ELSE WA END AS WA
FROM SCORE S
LEFT JOIN LASTACTIME L ON S.USER_ID = L.USER_ID
LEFT JOIN ENTRIES E ON E.USER_ID = S.USER_ID AND E.CONTEST_ID = 2
LEFT JOIN CNTWAALL C ON C.USER_ID = S.USER_ID
WHERE LAST_AC IS NOT NULL
),
R AS(
SELECT 
USER_ID,
POINT,
TIME+300*WA AS EX_TIME,
WA AS WRONG_ANS
FROM RANKING
)
SELECT
RANK() OVER(ORDER BY POINT DESC,EX_TIME) AS RANK,
R.*
FROM R