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

select 
    a.product_id,
    a.product_name,
    a.q2_2024_sales_total,
    row_number() over(partition by a.category order by a.q2_2024_sales_total desc) as category_rank,
    a.supplier_name
from
(
    select 
        a1.product_id,
        a1.product_name,
        sum(case when year(a2.order_date)=2024 and month(a2.order_date) between 4 and 6 
                 then a2.total_amount 
                 else 0 
            end) as q2_2024_sales_total,
        a1.category,
        a3.supplier_name
    from product_info as a1
    left outer join order_info as a2 on a1.product_id=a2.product_id
    left outer join supplier_info as a3 on a1.product_id=a3.product_id
    group by a1.product_id, a1.product_name, a1.category, a3.supplier_name
) as a
order by a.product_id asc