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 STRFTIME('%s', S.SUBMITTED_AT) < STRFTIME('%s', AC.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 S.CONTEST_ID = 2 AND S.STATUS <> 'AC' AND S.ENTRY_ID IS NOT NULL AND AC.AC_TIME IS NOT NULL
    GROUP BY S.PROBLEM_ID, S.USER_ID
),
CNTWAALL AS (
    SELECT 
        USER_ID,
        SUM(CNT_WA) AS WA
    FROM CNTWA
    GROUP BY USER_ID
),
RANKING AS (
    SELECT 
        S.USER_ID,
        S.SCORE AS POINT,
        L.LAST_AC - STRFTIME('%s', E.STARTED_AT) AS TIME,
        COALESCE(C.WA, 0) 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 L.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.USER_ID,
    R.POINT,
    R.EX_TIME,
    R.WRONG_ANS
FROM R
ORDER BY RANK;