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