with TRGT as (
  select
    *,
    rank() over (partition by PF_CODE order by AMT desc, NATION_CODE) as RANK
  from FOREIGNER
  where NATION_CODE != '113'
), TRGT2 as (
  select
    PF_CODE,
    max(case when RANK = 1 then NATION_CODE end) as RANK1_N,
    max(case when RANK = 1 then AMT end) as RANK1_A,
    max(case when RANK = 2 then NATION_CODE end) as RANK2_N,
    max(case when RANK = 2 then AMT end) as RANK2_A,
    max(case when RANK = 3 then NATION_CODE end) as RANK3_N,
    max(case when RANK = 3 then AMT end) as RANK3_A
  from TRGT
where rank <= 3
group by PF_CODE
)
select
  TRGT2.PF_CODE as "都道府県コード",
  P.PF_NAME as "都道府県名",
  N1.NATION_NAME as "1位 国名",
  TRGT2.RANK1_A as "1位 人数",
  N2.NATION_NAME as "2位 国名",
  TRGT2.RANK2_A as "2位 人数",
  N3.NATION_NAME as "3位 国名",
  TRGT2.RANK3_A as "3位 人数",
  S.SSUM as "合計人数"
from
  TRGT2
    inner join NATIONALITY N1 on TRGT2.RANK1_N = N1.NATION_CODE
    inner join NATIONALITY N2 on TRGT2.RANK2_N = N2.NATION_CODE
    inner join NATIONALITY N3 on TRGT2.RANK3_N = N3.NATION_CODE
    inner join (select PF_CODE, sum(AMT) as SSUM from TRGT group by PF_CODE) S on TRGT2.PF_CODE = S.PF_CODE
    inner join PREFECTURE P on TRGT2.PF_CODE = P.PF_CODE
order by
  "合計人数" desc,
  "都道府県コード"
;