WITH ranked_foreigners AS (
  SELECT
    f.PF_CODE,
    n.NATION_NAME,
    f.AMT,
    RANK() OVER (
      PARTITION BY f.PF_CODE
      ORDER BY f.AMT DESC, f.NATION_CODE ASC
    ) AS rank
  FROM FOREIGNER f
  JOIN NATIONALITY n ON f.NATION_CODE = n.NATION_CODE
  WHERE f.NATION_CODE != '113'
)

SELECT
  p.PF_CODE AS "都道府県コード",
  p.PF_NAME AS "都道府県名",
  MAX(CASE WHEN r.rank = 1 THEN r.NATION_NAME END) AS "1位 国名",
  MAX(CASE WHEN r.rank = 1 THEN r.AMT END) AS "1位 人数",
  MAX(CASE WHEN r.rank = 2 THEN r.NATION_NAME END) AS "2位 国名",
  MAX(CASE WHEN r.rank = 2 THEN r.AMT END) AS "2位 人数",
  MAX(CASE WHEN r.rank = 3 THEN r.NATION_NAME END) AS "3位 国名",
  MAX(CASE WHEN r.rank = 3 THEN r.AMT END) AS "3位 人数",
  SUM(r.AMT) AS "合計人数"
FROM PREFECTURE p
JOIN ranked_foreigners r ON p.PF_CODE = r.PF_CODE
WHERE r.rank <= 3
GROUP BY p.PF_CODE, p.PF_NAME
ORDER BY SUM(r.AMT) DESC, p.PF_CODE ASC;