SELECT
  PF_CODE as "都道府県コード",
  PF_NAME as "都道府県名",
  MAX(CASE WHEN row_num = 1 then NATION_NAME end) AS "1位 国名",
  MAX(CASE WHEN row_num = 1 then AMT end) AS "1位 人数",
  MAX(CASE WHEN row_num = 2 then NATION_NAME end) AS "2位 国名",
  MAX(CASE WHEN row_num = 2 then AMT end) AS "2位 人数",
  MAX(CASE WHEN row_num = 3 then NATION_NAME end) AS "3位 国名",
  MAX(CASE WHEN row_num = 3 then AMT end) AS "3位 人数",
  SUM(AMT) as "合計人数"
FROM
(select
  PF_CODE,
  PF_NAME,
  NATION_CODE,
  NATION_NAME,
  AMT,
  ROW_NUMBER() OVER (
    PARTITION BY PF_CODE
    ORDER BY AMT desc, NATION_CODE
) as row_num
FROM
  FOREIGNER
  join
  NATIONALITY
    using(NATION_CODE)
  join
  PREFECTURE
    using(PF_CODE)
WHERE
  NATION_CODE <> 113
) as FOREIGNER_ORDER
GROUP BY
1,2
order by
"合計人数" desc