with sum_quantity as (
select
p.product_id
,sum(quantity) quantity
from products p
right join orders o
on p.product_id = o.product_id
group by product_id
),
t as (
select 
sum_quantity.product_id product_id
,name
,category
,quantity total_sales
from 
sum_quantity inner join 
products on sum_quantity.product_id = products.product_id
)
select
name product_name
,total_sales
,row_number()over(partition by category order by total_sales desc,product_id) category_rank
from t