ソースコード
WITH tbl1 AS (
    SELECT
        CATEGORY_CODE,
        GENDER_CODE,
        PF_CODE,
        AMT
    FROM
        DRINK_HABITS
    WHERE
        GENDER_CODE IN ('2', '3')
    ),
tbl2 AS (
    SELECT
        PF_CODE,
        SUM(AMT) AS total_amt
    FROM
        tbl1
    WHERE
        CATEGORY_CODE = '110'
    GROUP BY
        PF_CODE
    ),
tbl3 AS (
    SELECT
        PF_CODE,
        SUM(AMT) AS drink_amt
    FROM
        tbl1
    WHERE
        CATEGORY_CODE = '120'
    GROUP BY
        PF_CODE
    ),
tbl4 AS (
    SELECT 
        tbl3.PF_CODE,
        ROUND(100 * CAST(tbl3.drink_amt AS real)/CAST(tbl2.total_amt AS real),1)  AS PERCENTAGE
    FROM
        tbl2
    LEFT JOIN
        tbl3
        ON tbl2.PF_CODE = tbl3.PF_CODE
    )
SELECT
    tbl4.PF_CODE AS CODE,
    PREFECTURE.PF_NAME AS NAME,
    tbl4.PERCENTAGE AS PERCENTAGE
FROM
    tbl4
INNER JOIN
    PREFECTURE
    ON tbl4.PF_CODE = PREFECTURE.PF_CODE
ORDER BY
    tbl4.PERCENTAGE DESC, tbl4.PF_CODE
提出情報
提出日時2022/09/21 21:10:53
コンテスト第2回 SQLコンテスト
問題飲酒率
受験者nash
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量101 MB
メッセージ
テストケース(通過数/総数)
1/2
状態
メモリ使用量
データパターン1
WA
101 MB
データパターン2
AC
100 MB