SELECT 
	p.name AS product_name,
    SUM(o.quantity) AS total_sales,
    RANK() OVER(partition by p.category
		order by SUM(o.quantity) DESC) AS category_rank
FROM products p
JOIN orders o USING (product_id)
GROUP BY p.name,RIGHT(category,1),p.product_id
ORDER BY category ASC,total_sales DESC,product_id ASC