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

WITH t AS (
SELECT t1.name AS product_name,
       SUM(t2.quantity) AS total_sales,
       t1.category,
       t1.product_id
FROM products t1
LEFT JOIN orders t2 ON t1.product_id=t2.product_id
GROUP BY t1.name,t1.category,t1.product_id
)
SELECT product_name,total_sales,
       ROW_NUMBER() OVER(PARTITION BY category ORDER BY total_sales DESC,product_id ASC) AS category_rank
FROM  t
WHERE total_sales>0