ソースコード
SELECT 
DENSE_RANK()OVER( ORDER BY POINT desc,EXEC_TIME asc ) AS RANK
,USER_ID
,POINT
,EXEC_TIME AS EX_TIME
,WRONG_ANS
FROM
(
SELECT
d.USER_ID
,d.EXEC_TIME + WRONG_ANS*300 AS EXEC_TIME
,d.POINT
,e.WRONG_ANS
FROM
(
    SELECt
    USER_ID
    ,MAX(TIME) AS EXEC_TIME
    ,SUM(POINT) AS POINT
    FROM
    (
    SELECT
    a.USER_ID
    ,a.SUBMITTED_AT
    ,b.STARTED_AT
    ,strftime('%s', a.SUBMITTED_AT)-strftime('%s',b.STARTED_AT) as TIME
    ,a.POINT
    FROM 
    (
        select 
        USER_ID
        ,SUBMITTED_AT
        ,POINT
        from SUBMISSIONS
        WHERE STATUS='AC'
        and CONTEST_ID=2
    )a
    INNER JOIN
    (
        SELECt *
        FROM ENTRIES
        where CONTEST_ID=2
    )b
    ON
    a.USER_ID=b.USER_ID
    )c
    GROUP BY 
    USER_ID
)d
INNER JOIN
(
    SELECT 
    USER_ID
    ,COUNT(*) AS WRONG_ANS
    FROM
    (
        select 
        a.USER_ID
        ,b.PROBLEM_ID
        ,a.STATUS
        FROM SUBMISSIONS a
        LEFT OUTER JOIN
        (
            select 
            USER_ID
            ,PROBLEM_ID
            ,SUBMITTED_AT
            from SUBMISSIONS
            where STATUS='AC'
        )b
        ON
        a.USER_ID=b.USER_ID
        and a.PROBLEM_ID=b.PROBLEM_ID
        where a.SUBMITTED_AT<b.SUBMITTED_AT
        and a.STATUS='WA'
    )c
    GROUP BY 
    USER_ID
)e
ON
d.USER_ID = e.USER_ID

)p
提出情報
提出日時2022/10/20 07:49:07
コンテスト第3回 SQLコンテスト
問題順位計算
受験者FunSQL
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量84 MB
メッセージ
テストケース(通過数/総数)
0/2
状態
メモリ使用量
データパターン1
WA
79 MB
データパターン2
WA
84 MB