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