WITH mart AS(
  SELECT
    FOREIGNER.PF_CODE,
    PF_NAME,
    FOREIGNER.NATION_CODE,
    NATION_NAME,
    AMT,
    ROW_NUMBER() OVER (PARTITION BY FOREIGNER.PF_CODE ORDER BY AMT DESC, FOREIGNER.NATION_CODE) AS ranking
  FROM
    FOREIGNER
  INNER JOIN
    NATIONALITY
  ON
    FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
  INNER JOIN
    PREFECTURE
  ON
    FOREIGNER.PF_CODE = PREFECTURE.PF_CODE
  WHERE
    FOREIGNER.NATION_CODE <> 113
  ORDER BY
    FOREIGNER.PF_CODE,
    AMT DESC,
    FOREIGNER.NATION_CODE
    
)

SELECT
PF_CODE AS 都道府県コード,
PF_NAME AS 都道府県名,
MAX(CASE WHEN ranking = 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名",
MAX(CASE WHEN ranking = 1 THEN AMT ELSE 0 END) AS "1位 人数",
MAX(CASE WHEN ranking = 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名",
MAX(CASE WHEN ranking = 2 THEN AMT ELSE 0 END) AS "2位 人数",
MAX(CASE WHEN ranking = 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名",
MAX(CASE WHEN ranking = 3 THEN AMT ELSE 0 END) AS "3位 人数",
SUM(AMT) AS "合計人数"
FROM
  mart
GROUP BY
  都道府県コード,
  都道府県名
ORDER BY
  合計人数 DESC,
  都道府県コード