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

SELECT
    product_name,
    total_sales,
    ROW_NUMBER() OVER(PARTITION BY t2.category ORDER BY t1.total_sales DESC) category_rank
FROM
    (
        SELECT
            p.product_id,
            p.name product_name,
            SUM(quantity) total_sales
        FROM
            orders o,
            products p
        WHERE
            o.product_id = p.product_id
        GROUP BY
            p.product_id,
            p.name
    ) t1,
    products t2
WHERE
    t1.product_id = t2.product_id
ORDER BY
    t2.category,
    t1.total_sales DESC,
    t2.product_id