ソースコード
WITH
TOTAL AS
(
    SELECT
        D.PF_CODE   AS CODE
    ,   SUM(D.AMT)  AS TOTAL_AMT
    FROM
        DRINK_HABITS D
    INNER JOIN
        PREFECTURE P 
        ON D.PF_CODE = P.PF_CODE
    WHERE
        D.CATEGORY_CODE = '110'
    AND D.GENDER_CODE IN ('2', '3')
    GROUP BY
        D.PF_CODE
),
EVERYDAY AS
(
    SELECT
        D.PF_CODE   AS CODE
    ,   SUM(D.AMT)  AS TOTAL_AMT
    FROM
        DRINK_HABITS D
    INNER JOIN
        PREFECTURE P 
        ON D.PF_CODE = P.PF_CODE
    WHERE
        D.CATEGORY_CODE = '120'
    AND D.GENDER_CODE IN ('2', '3')
    GROUP BY
        D.PF_CODE
)
SELECT
    P.PF_CODE AS CODE
,   P.PF_NAME AS NAME
,   ROUND(CAST(IFNULL(E.TOTAL_AMT, 0) AS REAL) / CAST(IFNULL(T.TOTAL_AMT, 1) AS REAL) * 100, 1) AS PERCENTAGE
FROM
    PREFECTURE P
LEFT JOIN
    TOTAL T
    ON P.PF_CODE = T.CODE
LEFT JOIN
    EVERYDAY E
    ON P.PF_CODE = E.CODE
ORDER BY
    PERCENTAGE DESC
,   CODE DESC
提出情報
提出日時2022/09/22 10:46:54
コンテスト第2回 SQLコンテスト
問題飲酒率
受験者zakk3456
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量101 MB
メッセージ
テストケース(通過数/総数)
1/2
状態
メモリ使用量
データパターン1
AC
83 MB
データパターン2
WA
101 MB