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

*/
SELECT
 t.PF_CODE as '都道府県コード'
,t.PF_NAME as '都道府県名'
,MAX(CASE t.RNK WHEN 1 THEN t.NATION_NAME ELSE 0 END) as '1位 国名'
,MAX(CASE t.RNK WHEN 1 THEN t.AMT ELSE 0 END) as '1位 人数'
,MAX(CASE t.RNK WHEN 2 THEN t.NATION_NAME ELSE 0 END) as '2位 国名'
,MAX(CASE t.RNK WHEN 2 THEN t.AMT ELSE 0 END) as '2位 人数'
,MAX(CASE t.RNK WHEN 3 THEN t.NATION_NAME ELSE 0 END) as '3位 国名'
,MAX(CASE t.RNK WHEN 3 THEN t.AMT ELSE 0 END) as '3位 人数'
,SUM(t.AMT) as '合計人数'
FROM (
    SELECT 
     p.PF_CODE --as 都道府県コード
    ,p.PF_NAME --as 都道府県名
    ,f.AMT
    ,n.NATION_NAME
    ,RANK() OVER(PARTITION BY p.PF_CODE ORDER BY f.AMT desc,p.PF_CODE) RNK
    FROM FOREIGNER f
    INNER JOIN PREFECTURE p
        ON p.PF_CODE = f.PF_CODE
    INNER JOIN NATIONALITY n
        ON n.NATION_CODE = f.NATION_CODE
        AND n.NATION_CODE <> '113'
) t
GROUP BY
 t.PF_CODE
,t.PF_NAME
ORDER BY 合計人数 desc,1 asc