ソースコード
WITH TEMP AS
(
    SELECT
         MAIN.USER_ID                                                                                   AS USER_ID
        ,MIN(MAIN.STARTED_AT)                                                                           AS STARTED_AT
        ,SUM(SUB.POINT)                                                                                 AS POINT
        ,SUM(CASE WHEN SUB.STATUS != 'AC' AND SUB.SUBMITTED_AT < MAX_SUB.MAX_AC_TIME THEN 1 ELSE 0 END) AS WRONG_ANS
        ,MAX(MAX_SUB.MAX_AC_TIME)                                                                       AS MAX_AC_TIME
    FROM ENTRIES AS MAIN
         INNER JOIN SUBMISSIONS AS SUB
         ON MAIN.ENTRY_ID = SUB.ENTRY_ID
         INNER JOIN (SELECT PROBLEM_ID, ENTRY_ID, MAX(SUBMITTED_AT) AS MAX_AC_TIME FROM SUBMISSIONS WHERE STATUS = 'AC' GROUP BY PROBLEM_ID, ENTRY_ID) AS MAX_SUB
         ON SUB.ENTRY_ID   = MAX_SUB.ENTRY_ID
        AND SUB.PROBLEM_ID = MAX_SUB.PROBLEM_ID
    WHERE MAIN.CONTEST_ID = 2
    GROUP BY MAIN.USER_ID
)
SELECT
     RANK() OVER(ORDER BY POINT DESC, EX_TIME, WRONG_ANS) AS RANK
    ,USER_ID
    ,POINT
    ,EX_TIME
    ,WRONG_ANS
FROM (
    SELECT
         USER_ID
        ,POINT
        ,strftime('%s', MAX_AC_TIME) - strftime('%s', STARTED_AT) + (300 * WRONG_ANS) AS EX_TIME
        ,WRONG_ANS
    FROM TEMP
)
WHERE POINT != 0
ORDER BY RANK, WRONG_ANS, USER_ID
;
提出情報
提出日時2022/10/19 19:07:05
コンテスト第3回 SQLコンテスト
問題順位計算
受験者jalapeno
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量96 MB
メッセージ
テストケース(通過数/総数)
1/2
状態
メモリ使用量
データパターン1
AC
92 MB
データパターン2
WA
96 MB