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

with t1 as 
(select a.product_id product_id,
product_name,
supplier_name,
total_amount,
order_date,
category
from product_info a 
join order_info b on a.product_id = b.product_id
join supplier_info c on a.product_id = c.product_id)

select product_id,
product_name,
sum(case when substring(order_date,7,1) between 4 and 6  then total_amount else 0 end) as q2_2024_sales_total,
rank()over(partition by category order by sum(case when substring(order_date,7,1) between 4 and 6  then total_amount else 0 end) desc) as category_rank,
supplier_name
from t1 
group by product_id
order by product_id