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

WITH
    t1 as (
        select
            category as product_category,
            age_group,
            sum(quantity * price) as total_sales_amount
        from
            products p
            JOIN sales s using (product_id)
            JOIN customer_info c using (sale_id)
        group by
            p.category,
            c.age_group
    ),
    t2 as (
        select
            product_category,
            sum(total_sales_amount) as total_cate_sale_amount
        from
            t1
        group by product_category
    )
select t1.product_category,
            t1.age_group,
            t1.total_sales_amount,
            round(t1.total_sales_amount/t2.total_cate_sale_amount,2) as purchase_percentage
            from t1
            join t2 on t1.product_category=t2.product_category
            order by product_category asc,age_group asc