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

WITH sub AS(
SELECT 
  p.name AS product_name ,
  SUM(o.quantity) AS total_sales,
  RANK() OVER(PARTITION BY p.category ORDER BY SUM(o.quantity) DESC) AS category_rank
FROM orders AS o
LEFT JOIN products AS p ON o.product_id = p.product_id
WHERE p.name IS NOT NULL                   --排除name缺失的无效数据
GROUP BY p.name, p.product_id, p.category  -- 增加category到分组中
ORDER BY p.category ASC, total_sales DESC   -- 按分类和排名排序
)
SELECT product_name,total_sales,category_rank
FROM sub