select
p.product_id product_id
,product_name
,sum(ifnull(total_amount,0)) q2_2024_sales_total
,dense_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
# where order_date between '2024-04-01' and '2024-06-30'
group by product_id , product_name, supplier_name 
order by product_id