题解 | 分析每个商品在不同时间段的销售情况

with zw as (
select
p.product_id,
p.product_name,
p.category,
date_format(o.order_date,'%Y-%m') as date,
ifnull(o.total_amount,0.00) as total_amount
from product_info p 
left join order_info o 
on p.product_id = o.product_id and date_format(o.order_date,'%Y-%m') in ('2024-04','2024-05','2024-06')
)
select 
zw.product_id,
zw.product_name,
sum(zw.total_amount) as q2_2024_sales_total,
row_number() over(partition by category order by sum(total_amount) desc) as category_rank,
su.supplier_name
from zw left join supplier_info su 
on zw.product_id = su.product_id
group by zw.product_id,zw.product_name,zw.category,su.supplier_name
order by zw.product_id;