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
),
START AS(
SELECT
DISTINCT S.USER_ID,
E.STARTED_AT
FROM SUBMISSIONS S 
LEFT JOIN ENTRIES E ON S.ENTRY_ID=E.ENTRY_ID
WHERE S.CONTEST_ID = 2 AND S.ENTRY_ID IS NOT NULL
),
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(strftime("%s",AC_TIME)) AS LAST_AC
FROM ACTIME
GROUP BY USER_ID
),
CNTWA AS(
SELECT
S.PROBLEM_ID,
S.USER_ID,
SUM(
CASE WHEN 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" AND ENTRY_ID IS NOT NULL AND AC.AC_TIME IS NOT NULL
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,
LAST_AC - strftime('%s',STARTED_AT) AS TIME,
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 START E ON E.USER_ID = S.USER_ID 
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
WHERE POINT > 0
)
SELECT
RANK() OVER(ORDER BY POINT DESC,EX_TIME) AS RANK,
R.*
FROM R