with rank_t as (
    select * ,
            rank() 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
    )
    
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 rank_t
    inner join PREFECTURE
        using(PF_CODE)
    inner join NATIONALITY
        using(NATION_CODE)
    inner join sum_t
        using(PF_CODE)
where "順位" <= 3
group by "PF_CODE","PF_NAME"
order by "合計人数" desc, "都道府県コード" desc
;