WITH POINTS AS (
SELECT
  T1.USER_ID AS USER_ID
  , SUM(T2.POINT) AS POINT
FROM ENTRIES T1
INNER JOIN SUBMISSIONS T2
ON T1.USER_ID = T2.USER_ID AND T1.ENTRY_ID = T2.ENTRY_ID
WHERE T1.CONTEST_ID = 2
AND T2.ENTRY_ID IS NOT NULL
AND T2.STATUS = 'AC'
GROUP BY T1.USER_ID
HAVING POINT > 0
), SOLVE_TIME AS (
SELECT
  T1.USER_ID AS USER_ID
  , strftime('%s', T2.MAX_SUBMITTED_AT) - strftime('%s', T1.STARTED_AT) AS AC_TIME
FROM ENTRIES T1
INNER JOIN (
  SELECT
    USER_ID
    , MAX(DATETIME(SUBMITTED_AT)) AS MAX_SUBMITTED_AT
  FROM SUBMISSIONS
  WHERE STATUS = 'AC' AND ENTRY_ID IS NOT NULL AND CONTEST_ID = 2
  GROUP BY USER_ID
) T2
ON T1.USER_ID = T2.USER_ID
WHERE T1.CONTEST_ID = 2
), WJ_COUNTS AS (
SELECT
  T1.USER_ID
  , SUM(T2.WJ_COUNT) AS WJ_COUNT
FROM ENTRIES T1
INNER JOIN (
  SELECT
    C1.USER_ID AS USER_ID
    , C1.PROBLEM_ID AS PROBLEM_ID
    , COUNT(CASE WHEN C1.ENTRY_ID IS NOT NULL AND C1.SUBMITTED_AT < C2.SUBMITTED_AT AND STATUS = 'WJ' THEN SUBMIT_ID ELSE NULL END) AS WJ_COUNT
  FROM SUBMISSIONS C1
  LEFT OUTER JOIN (SELECT USER_ID, PROBLEM_ID, SUBMITTED_AT FROM SUBMISSIONS WHERE STATUS = 'AC' AND CONTEST_ID = 2 AND ENTRY_ID IS NOT NULL) C2
  ON C1.USER_ID = C2.USER_ID AND C1.PROBLEM_ID = C2.PROBLEM_ID
  GROUP BY C1.USER_ID, C1.PROBLEM_ID
  ) T2
ON T1.USER_ID = T2.USER_ID
WHERE T1.CONTEST_ID = 2
GROUP BY T1.USER_ID
)
SELECT
  RANK() OVER (ORDER BY T1.POINT DESC, T2.AC_TIME + 300 * T3.WJ_COUNT ASC) AS RANK
  , T1.USER_ID AS USER_ID
  , T1.POINT AS POINT
  , T2.AC_TIME + 300 * T3.WJ_COUNT AS EX_TIME
  , T3.WJ_COUNT AS WRONG_ANS
FROM POINTS T1
INNER JOIN SOLVE_TIME T2 ON T1.USER_ID = T2.USER_ID
INNER JOIN WJ_COUNTS T3 ON T1.USER_ID = T3.USER_ID
ORDER BY
RANK ASC
, WRONG_ANS ASC
, USER_ID ASC
;