with
stats as (
    select
        foreigner.pf_code,
        pf_name,
        foreigner.nation_code,
        nation_name,
        
        sum(amt) as amt,
        row_number() over(partition by foreigner.pf_code order by amt desc) as rank
        
    from
        foreigner
    inner join (select * from nationality where nation_code != "113") as na
    on foreigner.nation_code = na.nation_code
    inner join prefecture
    on foreigner.pf_code = prefecture.pf_code
    group by
        foreigner.pf_code,
        pf_name,
        foreigner.nation_code,
        nation_name
    order by
        foreigner.pf_code,
        amt desc
),
lag_stats as (
    select
        pf_code,
        pf_name,
        nation_name,
        amt,
        rank,
        lead(nation_name, 1) over(partition by pf_code order by amt desc, nation_code) as lead1_name,
        lead(nation_name, 2) over(partition by pf_code order by amt desc, nation_code) as lead2_name,
        lead(amt, 1) over(partition by pf_code order by amt desc, nation_code) as lead1_amt,
        lead(amt, 2) over(partition by pf_code order by amt desc, nation_code) as lead2_amt
    from 
        stats
)
select
    lag_stats.PF_CODE as "都道府県コード",
    PF_NAME as "都道府県名",
    
    nation_name as "1位 国名",
    amt as "1位 人数",
    lead1_name as "2位 国名",
    lead1_amt as "2位 人数",
    lead2_name as "3位 国名",
    lead2_amt as "3位 人数",
    total_amt as "合計人数"
from 
    lag_stats
inner join (
    select
        pf_code,
        sum(amt) as total_amt
    from
        stats
    group by
        pf_code
) as total
on
    lag_stats.pf_code = total.pf_code
where
    rank = 1
    
order by
    "合計人数" desc,
    "都道府県コード"