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

WITH T1 AS (SELECT O.product_id, O.quantity, O.order_date, P.category, P.name
FROM orders O LEFT JOIN products P
ON O.product_id = P.product_id),

T2 AS (SELECT name AS product_name, 
SUM(quantity) 
AS total_sales
FROM T1
GROUP BY name),

T3 AS (SELECT T2.product_name, T2.total_sales, P.category FROM T2 LEFT JOIN 
products P ON T2.product_name = P.name)

SELECT product_name, total_sales,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC)AS category_rank
FROM T3
WHERE product_name IS NOT NULL