with sub as (
    select
        f.PF_CODE
        , p.PF_NAME
        , n.NATION_NAME
        , f.AMT
        , RANK() OVER (
            partition by f.PF_CODE
            order by 
                f.AMT desc
                , f.NATION_CODE
        ) as RANK
    from
        FOREIGNER as f
    inner join NATIONALITY as n
        on f.NATION_CODE = n.NATION_CODE
    inner join PREFECTURE as p
        on f.PF_CODE = p.PF_CODE
    where
        f.NATION_CODE != "113"
)

select
    PF_CODE as 都道府県コード
    , PF_NAME as 都道府県名
    , max(case RANK when 1 then NATION_NAME else NULL end ) as "1位 国名"
    , max(case RANK when 1 then AMT else NULL end ) as "1位 人数"
    , max(case RANK when 2 then NATION_NAME else NULL end ) as "2位 国名"
    , max(case RANK when 2 then AMT else NULL end ) as "2位 人数"
    , max(case RANK when 3 then NATION_NAME else NULL end ) as "3位 国名"
    , max(case RANK when 3 then AMT else NULL end ) as "3位 人数"
    , sum(AMT) as 合計人数
from
    sub
group by
    PF_CODE
order by
    合計人数 desc
    , PF_CODE
;