with a as
(select *
from order_info
where month(order_date) between 4 and 6),

b as     (select product_id,product_name,ifnull(sum(total_amount),0) as q2_2024_sales_total 
    ,row_number()over(partition by category order by ifnull(sum(total_amount),0)  desc) as category_rank
    from  product_info
    left join a
    using(product_id)

    group by product_id)

select b.*,supplier_name
from  b join supplier_info
using(product_id)
order by product_id asc