select
    sub.pf_code as "都道府県コード"
    , sub.pf_name as "都道府県名"
    , max(
        case
            when sub.rank=1 then sub.nation_name else null
        end
    ) as "1位 国名"
    , max(
        case
            when sub.rank=1 then amt else 0
        end
    ) as "1位 人数"
    , max(
        case
            when sub.rank=2 then sub.nation_name else null
        end
    ) as "2位 国名"
    , max(
        case
            when sub.rank=2 then amt else 0
        end
    ) as "2位 人数"
    , max(
        case
            when sub.rank=3 then sub.nation_name else null
        end
    ) as "3位 国名"
    , max(
        case
            when sub.rank=3 then sub.amt else 0
        end
    ) as "3位 人数"
    , sum(
        sub.amt
    ) as "合計人数"
from
    (
        select
            fo.pf_code as pf_code
            , pr.pf_name as pf_name
            , na.nation_name as nation_name
            , fo.amt as amt
            , rank() over (
                partition by
                    fo.pf_code
                order by
                    fo.amt desc,
                    fo.nation_code asc
            ) as rank
        from
            foreigner as fo
            inner join prefecture as pr
                on fo.pf_code = pr.pf_code
            inner join nationality as na
                on na.nation_code = fo.nation_code
        where
            fo.nation_code != "113"
    ) as sub
group by
    sub.pf_code
order by
    "合計人数" desc,
    "都道府県コード" asc;