WITH ACTIME AS (
  SELECT
    PROBLEM_ID,
    ENTRY_ID,
    SUBMITTED_AT,
    POINT
  FROM
    SUBMISSIONS
  WHERE
    STATUS = "AC"
    AND CONTEST_ID = 2
    AND ENTRY_ID IS NOT NULL
), WACNT AS (
  SELECT
    SUBMISSIONS.PROBLEM_ID,
    SUBMISSIONS.ENTRY_ID,
    SUM(CASE WHEN datetime(SUBMISSIONS.SUBMITTED_AT) <= datetime(ACTIME.SUBMITTED_AT) THEN 1 ELSE 0 END) AS WACNT
  FROM
    SUBMISSIONS
    INNER JOIN ACTIME ON SUBMISSIONS.PROBLEM_ID = ACTIME.PROBLEM_ID 
    AND SUBMISSIONS.ENTRY_ID = ACTIME.ENTRY_ID
  WHERE
    STATUS = "WA"
  GROUP BY
    SUBMISSIONS.PROBLEM_ID,
    SUBMISSIONS.ENTRY_ID
), WASUMS AS (
  SELECT
    ENTRY_ID,
    SUM(WACNT) AS WASUM
  FROM
    WACNT
  GROUP BY
    ENTRY_ID
), POINTSUM AS (
  SELECT
    ENTRY_ID,
    SUM(POINT) AS POINTSUM
  FROM
    ACTIME
  GROUP BY
    ENTRY_ID
), ACMAXTIME AS (
  SELECT
    ENTRY_ID,
    MAX(datetime(SUBMITTED_AT)) AS SUBMITTED_AT
  FROM
    ACTIME
  GROUP BY
    ENTRY_ID
)

SELECT
  RANK() OVER(
    ORDER BY 
      POINTSUM DESC, 
      strftime('%s', SUBMITTED_AT) - strftime('%s', STARTED_AT) + 300 * WASUM
  ) AS `RANK`,
  USER_ID AS `USER_ID`,
  POINTSUM AS `POINT`,
  strftime('%s', SUBMITTED_AT) - strftime('%s', STARTED_AT) + 300 * WASUM AS `EX_TIME`,
  WASUM AS `WRONG_ANS`
FROM
  ENTRIES
  LEFT JOIN WASUMS ON WASUMS.ENTRY_ID = ENTRIES.ENTRY_ID
  LEFT JOIN POINTSUM ON POINTSUM.ENTRY_ID = ENTRIES.ENTRY_ID
  LEFT JOIN ACMAXTIME ON ACMAXTIME.ENTRY_ID = ENTRIES.ENTRY_ID
WHERE
  CONTEST_ID = 2
  AND SUBMITTED_AT IS NOT NULL