ソースコード
WITH t AS(
    SELECT
        *
    FROM
        (
            SELECT
                PF_CODE AS pf, NATION_CODE AS nation, AMT, row_number() over (partition by PF_CODE order by AMT desc) as num
            FROM
                FOREIGNER
            WHERE
                nation <> 113
        )
    WHERE
        num <= 3
)
SELECT
    p.PF_CODE AS '都道府県コード', p.PF_NAME AS '都道府県名',
    (SELECT n.NATION_NAME FROM t t1 JOIN  NATIONALITY n ON n.NATION_CODE=t1.nation WHERE t1.pf = t.pf AND t1.num = 1) AS '1位 国名',
    (SELECT t1.AMT FROM t t1 WHERE t1.pf = t.pf AND t1.num = 1) AS '1位 人数',
    (SELECT n.NATION_NAME FROM t t2 JOIN  NATIONALITY n ON n.NATION_CODE=t2.nation WHERE t2.pf = t.pf AND t2.num = 2) AS '2位 国名',
    (SELECT t2.AMT FROM t t2 WHERE t2.pf = t.pf AND t2.num = 1) AS '2位 人数',
    (SELECT n.NATION_NAME FROM t t3 JOIN  NATIONALITY n ON n.NATION_CODE=t3.nation WHERE t3.pf = t.pf AND t3.num = 3) AS '3位 国名',
    (SELECT t3.AMT FROM t t3 WHERE t3.pf = t.pf AND t3.num = 1) AS '3位 人数',
    SUM(t.AMT) AS '合計人数'
FROM
    t
JOIN
    PREFECTURE p ON p.PF_CODE = t.pf
GROUP BY
    t.pf
ORDER BY
    合計人数 DESC,
    t.pf
;
提出情報
提出日時2022/09/22 15:34:03
コンテスト第1回 SQLコンテスト
問題外国籍分布
受験者ryusei
状態 (詳細)TLE
(Time Limit Exceeded: 時間制限オーバー)
メモリ使用量81 MB
メッセージ
テストケース(通過数/総数)
0/2
状態
メモリ使用量
データパターン1
TLE
81 MB
データパターン2
WA
78 MB