SELECT
    name AS product_name,
    SUM(quantity) as total_sales,
    ROW_NUMBER() OVER (
        PARTITION BY
            p.category
        ORDER BY
            SUM(quantity) DESC
    ) as category_rank
FROM
    orders as o
    JOIN products as p ON o.product_id = p.product_id
GROUP BY
    p.name,
    p.category
ORDER BY
    p.category,
    total_sales DESC,
    category_rank