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

①None值处理：sum(ifnull(total_amount,0))

②不显示product E：SQL执行顺序是先from...join...再where...，所以会被where中的月份筛掉，解决办法：将月份筛选放到join后面

select
    p.product_id,
    product_name,
    sum(ifnull(total_amount,0)) q2_2024_sales_total,
    rank() over(partition by category order by sum(total_amount) desc) category_rank,
    supplier_name
from
    product_info p 
left join order_info o on p.product_id=o.product_id and order_date between '2024-04-01' and '2024-06-30'
left join supplier_info s on p.product_id=s.product_id
group by p.product_id, product_name
order by p.product_id