select  product_name,total_sales,
row_number() over(partition by category order by total_sales desc) category_rank
from (
select name product_name,category
,sum(quantity) total_sales
from products a
join orders b
on a.product_id=b.product_id
group by product_name,category
order by category,total_sales desc
)c