with t1 as(
    select distinct name product_name,category,
    sum(quantity)over(partition by name) total_sales
    from products p join orders o on p.product_id = o.product_id
)
select product_name,total_sales,
rank()over(partition by category order by total_sales desc) category_rank 
from t1