ソースコード
WITH RankedData AS (
    SELECT
        h.AREA_CODE,
        AREA.AREA_NAME,
        SURVEY_YEAR,
        FOOD_EXP,
        CONSUMPTION_EXP,
        ROUND((1.0*FOOD_EXP / CONSUMPTION_EXP) * 100, 1) AS RATIO,
        DENSE_RANK() OVER (PARTITION BY SURVEY_YEAR ORDER BY ROUND((1.0*FOOD_EXP / CONSUMPTION_EXP) * 100, 1) DESC) AS RANK
    FROM
        HOUSEHOLD_SURVEY h
    INNER JOIN AREA ON h.AREA_CODE = AREA.AREA_CODE
    WHERE
        SURVEY_YEAR IN (2012, 2017, 2022)
)
SELECT
    r2022.AREA_CODE AS CODE,
    r2022.AREA_NAME AS NAME,
    r2022.RANK AS '2022_RANK',
    r2022.RATIO || '%' AS '2022_RATIO',
    r2017.RANK AS '2017_RANK',
    r2017.RATIO || '%' AS '2017_RATIO',
    r2012.RANK AS '2012_RANK',
    r2012.RATIO || '%' AS '2012_RATIO'
FROM
    RankedData r2022
LEFT JOIN
    RankedData r2017 ON r2022.AREA_CODE = r2017.AREA_CODE AND r2017.SURVEY_YEAR = 2017
LEFT JOIN
    RankedData r2012 ON r2022.AREA_CODE = r2012.AREA_CODE AND r2012.SURVEY_YEAR = 2012
WHERE
    r2022.SURVEY_YEAR = 2022
ORDER BY
    r2022.RANK ASC,
    r2022.AREA_CODE DESC;
提出情報
提出日時2023/12/17 12:34:44
コンテスト第10回 SQLコンテスト
問題食料費の割合
受験者swamp
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量85 MB
メッセージ
テストケース(通過数/総数)
0/3
状態
メモリ使用量
データパターン1
WA
85 MB
データパターン2
WA
84 MB
データパターン3
WA
85 MB