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