select name as product_name,total_sales,category_rank from
(
select p1.product_id,name,category,total_sales,
rank()over(partition by category order by total_sales desc) as category_rank
from products as p1
join
(
select product_id,sum(quantity) as total_sales from orders
group by product_id
) as p2
on p1.product_id = p2.product_id
) as t1