select t.product_id
,t.product_name
,sum(ifnull(t1.total_amount,0)) as q2_2024_sales_total
,row_number()over(partition by t.category order by sum(total_amount) desc) as category_rank
,supplier_name 
from product_info t
left join order_info t1 on t.product_id=t1.product_id and order_date BETWEEN '2024-04-01' AND '2024-06-30'
join supplier_info t2 on t.product_id=t2.product_id
group by t.product_id
,t.product_name
order by t.product_id