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

select 
    product_id, 
    product_name, 
    sum(case when substring(order_date,1,7) between "2024-04" and "2024-06" then total_amount else 0 end) q2_2024_sales_total, 
    rank()over(partition by category order by sum(case when substring(order_date,1,7) between "2024-04" and "2024-06" then total_amount else 0 end) desc) category_rank, 
    supplier_name
from product_info 
left join order_info using(product_id)
left join supplier_info using(product_id)
group by product_id
order by product_id

不能用where条件限定，否则产品e筛选不出来