select a.product_id, product_name,
sum(ifnull(total_amount, 0)) as q2_2024_sales_total,
row_number() over (partition by category order by sum(total_amount) desc) as category_rank,
supplier_name
from product_info a
left join (
    select *
    from order_info
    where year(order_date)=2024 and month(order_date) in (4, 5, 6)
) b on a.product_id=b.product_id
join supplier_info c on a.product_id=c.product_id
group by a.product_id, product_name
order by a.product_id