题解 | 每个商品的销售总额

SELECT
    t2.name AS product_name,
    SUM(t1.quantity) AS total_sales,
    RANK() OVER (PARTITION BY t2.category ORDER BY SUM(t1.quantity) DESC, t1.product_id) AS category_rank
FROM
    (
        SELECT
            product_id,
            SUM(quantity) AS quantity
        FROM
            orders
        GROUP BY
            product_id
    ) t1
JOIN
    (
        SELECT
            product_id,
            name,
            category
        FROM
            products
    ) t2
ON
    t1.product_id = t2.product_id
GROUP BY
    t2.category, t2.name,t1.product_id
ORDER BY
    t2.category, total_sales DESC;