select p.product_id,product_name,sum(ifnull(total_amount,0)) q2_2024_sales_total,
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'
join supplier_info s on p.product_id = s.product_id
group by p.product_id
order by p.product_id

时间筛选放在表连接的前面，null换成0就OK了