with first_table as (
    select
        PF_CODE, 
        MAX(AMT) as first
    from FOREIGNER a
    where NATION_CODE <> '113'
    group by PF_CODE
)
, second_table as (
    select
        a.PF_CODE, 
        MAX(AMT) as second
    from FOREIGNER a
    left join first_table b on a.PF_CODE = b.PF_CODE
    where a.AMT <> first AND NATION_CODE <> '113'
    group by a.PF_CODE
)
, third_table as (
    select
        a.PF_CODE, 
        MAX(AMT) as third
    from FOREIGNER a
    left join first_table b on a.PF_CODE = b.PF_CODE
    left join second_table c on a.PF_CODE = c.PF_CODE
    where a.AMT <> first
    AND a.AMT <> second
    AND NATION_CODE <> '113'
    group by a.PF_CODE
)
, sum_pre as(
    select
        sum(AMT) as 合計人数,
        PF_CODE
    from FOREIGNER
    where NATION_CODE <> '113'
    group by PF_CODE
)
, ORDERED_NATIONALITY as(
    select *
    from NATIONALITY
    order by NATION_CODE
)

select
    a.PF_CODE as "都道府県コード", 
    max(d.PF_NAME) as "都道府県名",
    max(CASE WHEN first = e.AMT THEN NATION_NAME END) as "1位 国名", 
    max(first) as "1位 人数", 
    max(CASE WHEN second = e.AMT THEN NATION_NAME END) as "2位 国名", 
    max(second) as "2位 人数", 
    max(CASE WHEN third = e.AMT THEN NATION_NAME END) as "3位 国名", 
    max(third) as "3位 人数", 
    合計人数 as "合計人数"
from first_table a
join second_table b on a.PF_CODE = b.PF_CODE
join third_table c on a.PF_CODE = c.PF_CODE
join PREFECTURE d on a.PF_CODE = d.PF_CODE
join FOREIGNER e on a.PF_CODE = d.PF_CODE
join ORDERED_NATIONALITY f on e.NATION_CODE=f.NATION_CODE
join sum_pre g on a.PF_CODE = g.PF_CODE
where first = e.AMT
or second = e.AMT
or third = e.AMT
group by a.PF_CODE
order by 合計人数 desc, 都道府県コード