SELECT
  p.PF_CODE AS "都道府県コード",
  p.PF_NAME AS "都道府県名",
  n.NATION_CODE,
  RANK() OVER(ORDER BY SUM(f.AMT) DESC) AS SCORE_RANK,
  SUM(f.AMT) AS "合計人数"
FROM
  FOREIGNER f
INNER JOIN
  NATIONALITY n ON n.NATION_CODE = f.NATION_CODE
INNER JOIN
  PREFECTURE p ON p.PF_CODE = f.PF_CODE
WHERE
  n.NATION_CODE <> "113"
GROUP BY
  p.PF_CODE,
  p.PF_NAME,
  n.NATION_CODE
ORDER BY
  SUM(f.AMT) DESC
;