with t1 as (select o.product_id as product_id,order_date,total_amount,product_name,category from order_info o
join product_info p
on o.product_id = p.product_id),
t2 as(
select t1.product_id,supplier_name,month(date(order_date)) as month_index,total_amount,product_name,category,
case 
when month(date(order_date)) between 4 and 6 then total_amount
else 0
end as replace_amount from supplier_info s 
join t1
on t1.product_id = s.product_id
),
t3 as (select product_id,product_name,sum(replace_amount) as q2_2024_sales_total,supplier_name,category from t2
group by product_id,product_name,supplier_name)
select product_id,product_name,q2_2024_sales_total,
rank()over(partition by category order by q2_2024_sales_total desc) as category_rank,
supplier_name from t3
order by product_id