with
    --都道府県ごとの順位
    --国籍その他除外
    rank_t as (
        select PF_CODE,
                NATION_CODE,
                AMT,
                row_number() over(partition by PF_CODE order by AMT desc,NATION_CODE asc) as "順位"
        from FOREIGNER
        where NATION_CODE <> '113'
    ),
    
    --国籍その他を除外した合計
    sum_t as (
        select PF_CODE,
                SUM(AMT) as "合計人数"
        from FOREIGNER
        where NATION_CODE <> '113'
        group by PF_CODE
    ),
    
    --都道府県ごとの上位3位と合計人数
    top_3 as (
        select PF_CODE,
                NATION_CODE,
                AMT,
                "順位",
                "合計人数"
        from rank_t inner join sum_t
            using(PF_CODE)
        where "順位" in (1,2,3)
    )
    
select PF_CODE as "都道府県コード",
        PF_NAME as "都道府県名",
        MAX(case "順位" when 1 then NATION_NAME else NULL end) as "1位 国名",
        MAX(case "順位" when 1 then AMT else 0 end) as "1位 人数",
        MAX(case "順位" when 2 then NATION_NAME else NULL end) as "2位 国名",
        MAX(case "順位" when 2 then AMT else 0 end) as "2位 人数",
        MAX(case "順位" when 3 then NATION_NAME else NULL end) as "3位 国名",
        MAX(case "順位" when 3 then AMT else 0 end) as "3位 人数",
        "合計人数"
from top_3
    inner join PREFECTURE
        using(PF_CODE)
    inner join NATIONALITY
        using(NATION_CODE)
group by "PF_CODE","PF_NAME"
order by "合計人数" desc, "都道府県コード" desc
;