with target as (
    select
        f.pf_code as pf_code,
        p.pf_name as pf_name,
        n.nation_name as nation_name,
        f.amt as amt,
        rank() over (
            partition by
                f.pf_code
            order by
                amt desc,
                f.nation_code
        ) as rank
    from
        foreigner as f
        inner join
            nationality as n
            on n.nation_code = f.nation_code
        inner join
            prefecture as p
            on p.pf_code = f.pf_code
    where
        f.nation_code != "113"
)

select
    pf_code as "都道府県コード",
    pf_name as "都道府県名",
    max(case t.rank when 1 then nation_name end) as "1位 国名",
    max(case t.rank when 1 then amt end) as "1位 人数",
    max(case t.rank when 2 then nation_name end) as "2位 国名",
    max(case t.rank when 2 then amt end) as "2位 人数",
    max(case t.rank when 3 then nation_name end) as "3位 国名",
    max(case t.rank when 3 then amt end) as "3位 人数",
    sum(amt) as "合計人数"
from
    target as t
group by
    t.pf_code
order by
    "合計人数" desc,
    "都道府県コード" asc