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

WITH category_total AS(
    select
        category,
        sum(quantity*price) AS category_total_sum
    FROM sales
    JOIN products USING(product_id)
    JOIN customer_info USING(sale_id)
    GROUP BY category
),
sub AS(
    select
        category,
        age_group,
        sum(quantity*price) AS total_sales_amount
    FROM sales
    JOIN products USING(product_id)
    JOIN customer_info USING(sale_id)
    GROUP BY category, age_group
)
SELECT
    category AS product_category,
    age_group,
    total_sales_amount,
    ROUND(total_sales_amount/category_total_sum, 2) AS purchase_percentage
FROM category_total JOIN sub USING(category)
ORDER BY product_category, age_group;