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