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

with t1 as(
select
p.product_id
, rank() over(partition by category order by total_amount desc ) category_rank
from product_info p
left join 
    (select product_id,sum(total_amount) total_amount
    from order_info
    where order_date between '2024-04-01' and '2024-06-30'
    group by 1) o 
on  p.product_id = o.product_id
order by 1)

select p.product_id
, p.product_name
, ifnull(sum(total_amount),0.00) q2_2024_sales_total
, 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 
left join t1 on p.product_id = t1.product_id
group by 1,2,4
order by 1