题解 | 对商品的销售情况进行深度分析

-- 思路拆解：按类别、年龄、性别分组，统计销售额；统计不同类别下，不同年龄的销售额占比
-- 第一步先求出各类别各年龄个性别分组的（分），第二不求出按照商品类别分组的（总），第三步再连接表，通过类别等于类别的条件
With 
    category_GMV AS(
    SELECT t1.category,t3.age_group,SUM(t2.quantity*t2.price) total_sales_amount
        FROM products t1
        INNER JOIN sales t2
        ON t1.product_id = t2.product_id
        INNER JOIN customer_info t3
        ON t2.sale_id = t3.sale_id
        GROUP BY t1.category,t3.age_group
    ),
    category_GMV_total AS(
    SELECT t1.category,SUM(t2.quantity*t2.price) total_sales
        FROM products t1
        INNER JOIN sales t2
        ON t1.product_id = t2.product_id
        INNER JOIN customer_info t3
        ON t2.sale_id = t3.sale_id
        GROUP BY t1.category
    )
SELECT 
t1.category product_category,
t1.age_group,
t1.total_sales_amount,
ROUND(t1.total_sales_amount/t2.total_sales,2) purchase_percentage
FROM category_GMV t1
INNER JOIN category_GMV_total t2
ON t1.category = t2.category
ORDER BY t1.category,t1.age_group