select a.product_id,product_name,
sum(case when total_amount is not null then total_amount else 0 end) as q2_2024_sales_total,
rank() over(partition by category order by sum(total_amount) desc) as category_rank,
supplier_name
from product_info a 
left join order_info b
on a.product_id = b.product_id and order_date between '2024-04-01' and '2024-06-30'
left join supplier_info c 
on a.product_id = c.product_id
group by a.product_id 
order by a.product_id