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

with temp0 as (
        select
            pi.product_id,
            sum(if(order_date between '2024-04-01' and '2024-06-30',total_amount,0)) as q2_2024_sales_total
        from product_info pi left join order_info oi on pi.product_id = oi.product_id
        group by pi.product_id
    ),
    temp1 as (
        select
            t0.product_id,
            product_name,
            q2_2024_sales_total,
            rank() over (partition by category order by q2_2024_sales_total desc ) as category_rank
        from temp0 t0 inner join product_info pi on t0.product_id = pi.product_id
    )

select
    t1.product_id,
    product_name,
    q2_2024_sales_total,
    category_rank,
    supplier_name
from temp1 t1 inner join supplier_info si on t1.product_id = si.product_id
order by t1.product_id asc;