DataScience100Knock-S19
S-019: レシート明細テーブル（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭10件を抽出せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合は同一順位を付与するものとする。
最終的にこうなった
code:sql
select
customer_id,
amount,
rank() over(order by amount desc)
from receipt
limit 10;
最初はこう書いていた
code:1.sql
select
receipt.customer_id,
receipt.amount,
ranking.rank
from receipt
inner join
(
select amount, row_number() over(order by amount desc) as rank
from (select distinct(amount) amount from receipt
order by amount desc
limit 10) as highranker
) as ranking
on receipt.amount = ranking.amount
order by ranking.rank
limit 10;
rank()の存在に気づかなかったので row_number()でできるように苦戦した。
せめてwithを使うと次のようになる
code: 2.sql
with highranker as (
select distinct(amount) amount from receipt
order by amount desc
limit 10
), ranking as (
select amount, row_number() over(order by amount desc) as rank
from highranker
)
select
receipt.customer_id,
receipt.amount,
ranking.rank
from receipt
inner join ranking
on receipt.amount = ranking.amount
order by ranking.rank
limit 10;