ソースコード
with base as (
select
pf_code
,NATION_CODE
,AMT
,sum(AMT)over (PARTITION BY pf_code)  as total
,DENSE_RANK() over (PARTITION BY pf_code order by AMT desc, pf_code) as ranking
from FOREIGNER
where NATION_CODE != '113'
group by 1,2,3
)
select
PREFECTURE.pf_code as "都道府県コード"
,PREFECTURE.pf_name as  "都道府県名"
,max(case when ranking = 1  then nation_name end) as  "1位 国名"
,max(case when ranking = 1  then AMT end) as  "1位 人数"
,max(case when ranking = 2  then nation_name end) as  "2位 国名"
,max(case when ranking = 2  then AMT end) as  "2位 人数"
,max(case when ranking = 3  then nation_name end) as  "3位 国名"
,max(case when ranking = 3  then AMT end) as  "3位 人数"
,max(case when ranking = 1  then total end) as  "合計人数"
from base 
inner join PREFECTURE on base.pf_code = PREFECTURE.pf_code
inner join NATIONALITY on base.NATION_CODE = NATIONALITY.NATION_CODE
group by 1,2
order by max(case when ranking = 1  then total end) desc, PREFECTURE.pf_code
提出情報
提出日時2022/07/20 21:25:06
コンテスト第1回 SQLコンテスト
問題外国籍分布
受験者pontago
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量102 MB
メッセージ
テストケース(通過数/総数)
1/2
状態
メモリ使用量
データパターン1
WA
91 MB
データパターン2
AC
102 MB