WITH TOP_CO AS (
  SELECT PF_CODE
    , NATION_NAME
    , AMT
    , RANK() OVER(
      PARTITION BY PF_CODE
      ORDER BY AMT DESC, NATION_CODE
    ) AS AMT_RANK
    FROM FOREIGNER
    NATURAL JOIN NATIONALITY
    WHERE NATION_CODE <> '113'
  )
SELECT PF_CODE AS '都道府県コード'
  , PF_NAME AS '都道府県名'
  , MAX(CASE AMT_RANK WHEN 1 THEN NATION_NAME ELSE NULL END) AS '1位 国名'
  , MAX(CASE AMT_RANK WHEN 1 THEN AMT ELSE NULL END) AS '1位 人数'
  , MAX(CASE AMT_RANK WHEN 2 THEN NATION_NAME ELSE NULL END) AS '2位 国名'
  , MAX(CASE AMT_RANK WHEN 2 THEN AMT ELSE NULL END) AS '2位 人数'
  , MAX(CASE AMT_RANK WHEN 3 THEN NATION_NAME ELSE NULL END) AS '3位 国名'
  , MAX(CASE AMT_RANK WHEN 3 THEN AMT ELSE NULL END) AS '3位 人数'
  , SUM(AMT) AS '合計人数'
FROM TOP_CO
NATURAL JOIN PREFECTURE
GROUP BY 1, 2
ORDER BY 9 DESC, 1 ;