ソースコード
WITH t1 AS(
    SELECT
        *,
        row_number() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS rn
    FROM FOREIGNER
    WHERE nation_code != '113'
)
, t2 AS(
    SELECT
        PF_CODE,
        SUM(CASE rn WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数",
        MAX(CASE rn WHEN 1 THEN NATION_CODE ELSE "" END) AS nation_1,
        SUM(CASE rn WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数",
        MAX(CASE rn WHEN 2 THEN NATION_CODE ELSE "" END) AS nation_2,
        SUM(CASE rn WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数",
        MAX(CASE rn WHEN 3 THEN NATION_CODE ELSE "" END) AS nation_3
    FROM t1
    GROUP BY PF_CODE
)

, t3 AS(
    SELECT PF_CODE, sum(AMT) AS "合計人数"
    FROM t1
    -- WHERE rn < 4
    GROUP BY PF_CODE
)

SELECT 
    pf_code,
    pf_name,
    n1.nation_name AS "1位 国名",
    "1位 人数",
    n2.nation_name AS "2位 国名",
    "2位 人数",
    n3.nation_name AS "3位 国名",
    "3位 人数",
    "合計人数"
FROM t2
LEFT JOIN prefecture using(pf_code)
LEFT JOIN t3 using(pf_code)
LEFT JOIN nationality n1 on t2.nation_1 = n1.nation_code
LEFT JOIN nationality n2 on t2.nation_2 = n2.nation_code
LEFT JOIN nationality n3 on t2.nation_3 = n3.nation_code
ORDER BY "合計人数" DESC, pf_code
提出情報
提出日時2022/07/20 20:36:53
コンテスト第1回 SQLコンテスト
問題外国籍分布
受験者k.minowa
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量103 MB
メッセージ
テストケース(通過数/総数)
0/2
状態
メモリ使用量
データパターン1
WA
103 MB
データパターン2
WA
85 MB