-- 都道府県に在住している外国籍の人数を集計した外国人データから、各都道府県で人数が多い国のベスト３を表示しなさい。ただし、国籍コード= '113'（その他）のデータは除いて集計すること。 また、集計した人数が同数の場合は、国籍コードの昇順で順位付けを行うこと。
-- 
-- 表示項目は以下とする。(エイリアスを使用し→の項目名とする)
-- 
-- PF_CODE  as "都道府県コード",
-- PF_NAME  as "都道府県名",
-- 人数が一番多い国のNATION_NAME  as "1位 国名",
-- 人数が一番多い国のAMT  as "1位 人数",
-- 人数が二番目に多い国のNATION_NAME  as "2位 国名",
-- 人数が二番目に多い国のAMT  as "2位 人数",
-- 人数が三番目に多い国のNATION_NAME  as "3位 国名",
-- 人数が三番目に多い国のAMT  as "3位 人数",
-- 各都道府県毎の外国人の合計人数  as "合計人数"
-- 表示順
-- 
-- 合計人数の降順
-- 都道府県コードの昇順

with non113 as (
  select PF_CODE, NATION_CODE, NATION_NAME, AMT
  from FOREIGNER
  inner join NATIONALITY using(NATION_CODE)
  WHERE NATION_CODE != '113'
),
rnk as (
   SELECT
     *, ROW_NUMBER() OVER (PARTITION BY PF_CODE ORDER BY AMT desc, NATION_CODE) as r
   FROM non113
),
top123 as (
  SELECT PF_CODE,
         max(CASE WHEN r = 1 THEN NATION_NAME END) AS nname1,
         max(CASE WHEN r = 1 THEN AMT END) AS amt1,
         max(CASE WHEN r = 2 THEN NATION_NAME END) AS nname2,
         max(CASE WHEN r = 2 THEN AMT END) AS amt2,
         max(CASE WHEN r = 3 THEN NATION_NAME END) AS nname3,
         max(CASE WHEN r = 3 THEN AMT END) AS amt3
  FROM rnk
  GROUP BY PF_CODE
),
sum_amt as (
  SELECT PF_CODE, sum(AMT) as all_amt
  from non113
  group by PF_CODE
)
select 
 PF_CODE  as "都道府県コード",
 PF_NAME  as "都道府県名",
 nname1  as "1位 国名",
 amt1  as "1位 人数",
 nname2  as "2位 国名",
 amt2  as "2位 人数",
 nname3  as "3位 国名",
 amt3  as "3位 人数",
 all_amt  as "合計人数"
from top123
inner join PREFECTURE using(PF_CODE)
left join sum_amt using(PF_CODE)
order by all_amt desc, PF_CODE