SELECT
    A.name AS product_name,
    SUM(B.quantity) AS total_sales,
    RANK() OVER (
        partition BY
            A.category
        ORDER BY
            SUM(B.quantity) DESC
    ) AS category_rank
FROM
    products A
    JOIN orders B ON A.product_id = B.product_id
GROUP BY
    B.product_id