-- 都道府県に在住している外国籍の人数を集計した外国人データから、
-- 各都道府県で人数が多い国のベスト３を表示しなさい。
-- ただし、国籍コード= '113'（その他）のデータは除いて集計すること。 
-- また、集計した人数が同数の場合は、国籍コードの昇順で順位付けを行うこと。
select 
sub.PF_CODE as 都道府県コード
,sub.PF_NAME as 都道府県名
-- 人数が一番多い国のNATION_NAME → 1位 国名
,max(case when sub.rank = 1 then sub.NATION_NAME else NULL end) as '1位 国名'
-- 人数が一番多い国のAMT → 1位 人数
,max(case when sub.rank = 1 then sub.AMT else 0 end) as '1位 人数'
-- 人数が二番目に多い国のNATION_NAME → 2位 国名
,max(case when sub.rank = 2 then sub.NATION_NAME else NULL end) as '2位 国名'
-- 人数が二番目に多い国のAMT → 2位 人数
,max(case when sub.rank = 2 then sub.AMT else 0 end) as '2位 人数'
-- 人数が三番目に多い国のNATION_NAME → 3位 国名
,max(case when sub.rank = 3 then sub.NATION_NAME else NULL end) as '3位 国名'
-- 人数が三番目に多い国のAMT → 3位 人数
,max(case when sub.rank = 3 then sub.AMT else 0 end) as '3位 人数'
-- 各都道府県毎の外国人の合計人数 → 合計人数
,sum(sub.AMT) as 合計人数
from (
select
-- 表示項目は以下とする。(エイリアスを使用し→の項目名とする)
p.PF_CODE
,p.PF_NAME
,n.NATION_NAME
,f.AMT
,rank() OVER(PARTITION BY p.PF_CODE ORDER BY f.AMT desc) rank
from FOREIGNER f 
inner join PREFECTURE p on f.PF_CODE = p.PF_CODE
inner join NATIONALITY n on f.NATION_CODE = n.NATION_CODE
where f.NATION_CODE != '113' )  sub
group by sub.PF_CODE,sub.PF_NAME
-- 表示順
order by 
-- 合計人数の降順
合計人数 desc
-- 都道府県コードの昇順
,都道府県コード asc