with o as(
select*from order_info where date_format(order_date,'%Y-%m') between '2024-04' and '2024-06')
select p.product_id,p.product_name,sum(ifnull(o.total_amount,0)) as q2_2024_sales_total,
row_number()over(partition by p.category order by sum(o.total_amount) desc) as category_rank,
s.supplier_name
from product_info p left join o on p.product_id=o.product_id
left join supplier_info s on p.product_id=s.product_id
group by p.product_id
order by p.product_id