ソースコード
-- 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 POINT,
    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 POINT > 0
)
SELECT RANK () OVER (
    ORDER BY POINT DESC,
      EX_TIME
  ) AS RANK,
  USER_ID,
  POINT,
  EX_TIME,
  WRONG_ANS
FROM temp_cte
ORDER BY RANK,
  WRONG_ANS,
  USER_ID;
提出情報
提出日時2023/11/09 18:31:27
コンテスト第3回 SQLコンテスト
問題順位計算
受験者vesper
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量86 MB
メッセージ
テストケース(通過数/総数)
1/2
状態
メモリ使用量
データパターン1
AC
85 MB
データパターン2
WA
86 MB