WITH foreigner_ranking AS (
  SELECT
    pf.pf_code,
    pf.pf_name,
    RANK() OVER (PARTITION BY pf.pf_code ORDER BY f.amt DESC, f.nation_code) AS ranking,
    n.nation_name,
    f.amt
  FROM
    foreigner f
    JOIN nationality n ON f.nation_code = n.nation_code
    JOIN prefecture pf ON f.pf_code = pf.pf_code
  WHERE
    f.nation_code <> '113'
)
SELECT
  pf_code AS "都道府県コード",
  pf_name AS "都道府県名",
  MAX(CASE WHEN ranking = 1 THEN nation_name END) AS "1位 国名",
  MAX(CASE WHEN ranking = 1 THEN amt END) AS "1位 人数",
  MAX(CASE WHEN ranking = 2 THEN nation_name END) AS "2位 国名",
  MAX(CASE WHEN ranking = 2 THEN amt END) AS "2位 人数",
  MAX(CASE WHEN ranking = 3 THEN nation_name END) AS "3位 国名",
  MAX(CASE WHEN ranking = 3 THEN amt END) AS "3位 人数",
  SUM(amt) AS "合計人数"
FROM
  foreigner_ranking
GROUP BY
  pf_code, pf_name
ORDER BY
  "合計人数" DESC, pf_code;