select
    PF_CODE AS '都道府県コード',
    PF_NAME AS '都道府県名',
    max(case RANK when 1 then NATION_NAME end) as "1位 国名",
    max(case RANK when 1 then AMT end) as "1位 人数",
    max(case RANK when 2 then NATION_NAME end) as "2位 国名",
    max(case RANK when 2 then AMT end) as "2位 人数",
    max(case RANK when 3 then NATION_NAME end) as "3位 国名",
    max(case RANK when 3 then AMT end) as "3位 人数",
    sum(AMT) as "合計人数"
from
    (
        select
            f.PF_CODE as PF_CODE,
            p.PF_NAME as PF_NAME,
            n.NATION_NAME as NATION_NAME,
            f.AMT as AMT,
            row_number() over (
                partition by
                    f.PF_CODE
                order by
                    f.AMT desc,
                    f.NATION_CODE asc
            ) 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
            n.NATION_CODE != '113'
    )
group by
    PF_CODE
order by
    合計人数 DESC,
    都道府県コード ASC
;