DataScience100Knock-S39

S-039: レシート明細テーブル（receipt）から売上日数の多い顧客の上位20件と、売上金額合計の多い顧客の上位20件を抽出し、完全外部結合せよ。ただし、非会員（顧客IDが'Z'から始まるもの）は除外すること。

code:ans.sql

with c as

(

select

customer_id,

count(distinct(sales_ymd)) as salesdates,

sum(amount) as sum_amount

from receipt

where customer_id not like 'Z%'

group by customer_id

), sds as

(

select *

from c

order by salesdates desc

limit 20

), ams as

(

select *

from c

order by sum_amount desc

limit 20

)

select

coalesce(sds.customer_id, ams.customer_id) as customer_id,

coalesce(sds.salesdates, ams.salesdates) as salesdates,

coalesce(sds.sum_amount, ams.sum_amount) as sum_amount

from sds

full outer join ams

on sds.customer_id = ams.customer_id

order by sum_amount desc;