题解 | 对商品的销售情况进行深度分析
对商品的销售情况进行深度分析
https://www.nowcoder.com/practice/d6ced1b60af64a4998169ae717672e8e
WITH general AS (
SELECT a.category AS product_category, c.age_group,
SUM(b.quantity * b.price) AS total_sales_amount
FROM customer_info AS c
LEFT JOIN sales AS b
ON b.sale_id = c.sale_id
LEFT JOIN products AS a
ON a.product_id = b.product_id
GROUP BY a.category, c.age_group
)
SELECT product_category, age_group, total_sales_amount,
ROUND((total_sales_amount /
SUM(total_sales_amount) OVER (PARTITION BY product_category)), 2) AS purchase_percentage
FROM general
ORDER BY product_category, age_group;
