-- 各参加者の合計点数、解答時間、誤答数を計算するクエリ
WITH
  -- 各エントリーごとの最後のACの提出結果を抽出
  last_ac_submissions AS (
    SELECT
      e.USER_ID,
      s.PROBLEM_ID,
      MAX(s.SUBMITTED_AT) AS LAST_AC_SUBMITTED_AT
    FROM
      ENTRIES e
      INNER JOIN SUBMISSIONS s ON e.ENTRY_ID = s.ENTRY_ID
    WHERE
      e.CONTEST_ID = 2
      AND s.STATUS = 'AC'
    GROUP BY
      e.USER_ID,
      s.PROBLEM_ID
  ),
  -- 各参加者の合計点数と問題別誤答数を計算
  user_scores AS (
    SELECT
      e.USER_ID,
      SUM(s.POINT) AS POINT,
      COUNT(DISTINCT s.PROBLEM_ID) - COUNT(la.PROBLEM_ID) AS WRONG_ANS
    FROM
      ENTRIES e
      LEFT JOIN SUBMISSIONS s ON e.ENTRY_ID = s.ENTRY_ID
      LEFT JOIN last_ac_submissions la ON e.USER_ID = la.USER_ID AND s.PROBLEM_ID = la.PROBLEM_ID AND s.SUBMITTED_AT > la.LAST_AC_SUBMITTED_AT
    WHERE
      e.CONTEST_ID = 2
    GROUP BY
      e.USER_ID
  ),
  -- 各参加者の解答時間を計算
  user_times AS (
    SELECT
      e.USER_ID,
      (strftime('%s', MAX(s.SUBMITTED_AT)) - strftime('%s', e.STARTED_AT)) AS TIME_DIFF
    FROM
      ENTRIES e
      INNER JOIN SUBMISSIONS s ON e.ENTRY_ID = s.ENTRY_ID
    WHERE
      e.CONTEST_ID = 2
    GROUP BY
      e.USER_ID
  ),
  -- 各参加者の最終的な解答時間を計算
  user_ex_times AS (
    SELECT
      ut.USER_ID,
      ut.TIME_DIFF + (300 * us.WRONG_ANS) AS EX_TIME
    FROM
      user_times ut
      INNER JOIN user_scores us ON ut.USER_ID = us.USER_ID
  ),
  -- 合計した点数が０点でないデータのみを集計
  valid_scores AS (
    SELECT
      us.USER_ID,
      us.POINT,
      us.WRONG_ANS,
      ue.EX_TIME
    FROM
      user_scores us
      INNER JOIN user_ex_times ue ON us.USER_ID = ue.USER_ID
    WHERE
      us.POINT > 0
  ),
  -- 順位を計算
  ranked_scores AS (
    SELECT
      USER_ID,
      POINT,
      WRONG_ANS,
      EX_TIME,
      RANK() OVER (ORDER BY POINT DESC, EX_TIME ASC, WRONG_ANS ASC, USER_ID ASC) AS RANK
    FROM
      valid_scores
  )
-- 集計結果の表示
SELECT
  RANK AS "RANK",
  USER_ID AS "USER_ID",
  POINT AS "POINT",
  EX_TIME AS "EX_TIME",
  WRONG_ANS AS "WRONG_ANS"
FROM
  ranked_scores
ORDER BY
  RANK ASC, WRONG_ANS ASC, USER_ID ASC;