-- https://topsic-contest.jp/contests/contest003/problems/contest003-4
WITH valid_submissions as (
  SELECT ENTRY_ID,
    SUBMITTED_AT,
    POINT
  FROM SUBMISSIONS
  WHERE ENTRY_ID IS NOT NULL
    AND CONTEST_ID = 2
    AND STATUS = "AC"
),
last_sub_cte as (
  SELECT ENTRY_ID,
    MAX(SUBMITTED_AT) as last_submission
  FROM valid_submissions
  GROUP BY ENTRY_ID
),
total_point_cte as (
  SELECT ENTRY_ID,
    SUM(POINT) as total
  FROM valid_submissions
  GROUP BY ENTRY_ID
),
wrong_answer_cte as (
  SELECT ENTRY_ID,
    SUM(n - 1) as WRONG_ANS
  FROM (
      SELECT ROW_NUMBER() OVER(
          PARTITION BY ENTRY_ID,
          PROBLEM_ID
          ORDER BY SUBMITTED_AT
        ) as n,
        STATUS,
        ENTRY_ID
      FROM SUBMISSIONS
      WHERE ENTRY_ID IS NOT NULL
    ) AS numbered_submissions
  WHERE STATUS = "AC"
  GROUP BY ENTRY_ID
),
temp_cte AS (
  SELECT USER_ID,
    total AS SCORE,
    strftime('%s', last_submission) - strftime('%s', STARTED_AT) + WRONG_ANS * 300 as EX_TIME,
    WRONG_ANS
  FROM ENTRIES
    JOIN last_sub_cte USING (ENTRY_ID)
    JOIN total_point_cte USING (ENTRY_ID)
    JOIN wrong_answer_cte USING (ENTRY_ID)
  WHERE SCORE > 0
)
SELECT RANK () OVER (
    ORDER BY SCORE DESC,
      EX_TIME
  ) AS RANK,
  USER_ID,
  SCORE,
  EX_TIME,
  WRONG_ANS
FROM temp_cte
ORDER BY RANK,
  WRONG_ANS,
  USER_ID;