WITH country AS (
  SELECT
    *
  FROM
    nationality
  WHERE
    nation_code != '113'
),
country_rank AS (
  SELECT
    pf_code,
    pf_name,
    nation_code,
    nation_name,
    amt,
    row_number() over ( partition by pf_code
      ORDER BY
        amt DESC,
        nation_code
    ) as amt_rank
  FROM
    foreigner
    INNER JOIN prefecture USING (pf_code)
    INNER join country using(nation_code)
)

SELECT
PF_CODE as 都道府県コード,
PF_NAME as 都道府県名,
max(CASE WHEN amt_rank = 1 THEN NATION_NAME END) as "1位国名",
max(CASE WHEN amt_rank = 1 THEN AMT END) as "1位人数",
max(CASE WHEN amt_rank = 2 THEN NATION_NAME END) as "2位国名",
max(CASE WHEN amt_rank = 2 THEN AMT END) as "2位人数",
max(CASE WHEN amt_rank = 3 THEN NATION_NAME END) as "3位国名",
max(CASE WHEN amt_rank = 3 THEN AMT END) as "3位人数",
sum(amt) as 合計人数
FROM
  country_rank
group by
  pf_code
  ,pf_name
ORDER BY
  合計人数 DESC,
  都道府県コード