WITH LPAC AS (
    SELECT
        ENTRY_ID,
        PROBLEM_ID,
        SUM(POINT) AS POINT,
        MAX(SUBMITTED_AT) AS SUBMITTED_AT
    FROM
        SUBMISSIONS
    WHERE
        CONTEST_ID=2 AND STATUS = 'AC'
    GROUP BY
        ENTRY_ID,
        PROBLEM_ID
)
, PWA AS (
    SELECT
        S.ENTRY_ID,
        S.PROBLEM_ID,
        COUNT() AS COUNT
    FROM
        SUBMISSIONS S
        LEFT OUTER JOIN
        LPAC
        ON
            S.ENTRY_ID = LPAC.ENTRY_ID
            AND S.PROBLEM_ID = LPAC.PROBLEM_ID
    WHERE
        S.CONTEST_ID=2
        AND S.SUBMITTED_AT < LPAC.SUBMITTED_AT
        AND STATUS = 'WA'
    GROUP BY
        S.ENTRY_ID
),
LAC AS (
    SELECT
        ENTRY_ID,
        SUM(POINT) AS POINT,
        MAX(SUBMITTED_AT) AS SUBMITTED_AT
    FROM
        LPAC
    GROUP BY
        ENTRY_ID
)
, WA AS (
    SELECT
        ENTRY_ID,
        SUM(COUNT) AS COUNT
    FROM
        PWA
    GROUP BY
        ENTRY_ID
),
D AS (
SELECT
    E.USER_ID AS USER_ID,
    LAC.POINT AS POINT,
    ( strftime('%s',LAC.SUBMITTED_AT) - strftime('%s',E.STARTED_AT) ) + CASE WHEN WA.COUNT IS NULL THEN 0 ELSE WA.COUNT * 300 END AS EX_TIME,
    CASE WHEN WA.COUNT IS NULL THEN 0 ELSE WA.COUNT END AS WRONG_ANS
FROM
    ENTRIES E
    LEFT OUTER JOIN
    LAC
    ON
        E.ENTRY_ID = LAC.ENTRY_ID
    LEFT OUTER JOIN
    WA
    ON
        E.ENTRY_ID = WA.ENTRY_ID
WHERE
    E.CONTEST_ID=2
GROUP BY
    E.USER_ID,
    E.ENTRY_ID
HAVING
    SUM(LAC.POINT)>0 AND SUM(LAC.POINT) IS NOT NULL
)
SELECT
    RANK() OVER ( ORDER BY POINT DESC, EX_TIME ASC ) AS RANK,
    USER_ID,
    POINT,
    EX_TIME,
    WRONG_ANS
FROM
    D
ORDER BY
    1 ASC,
    5 ASC,
    2 ASC