select a.name as product_name,sum(b.quantity) as total_sales,

row_number() over(partition by a.category order by sum(b.quantity) desc) as category_rank from

products a join orders b on a.product_id=b.product_id

group by a.name,a.category