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

select
        a.product_category,
        a.age_group,
        a.total_sales_amount,
        round(a.total_sales_amount / b.category_sum_amount,2) as purchase_percentage
from (
    select
        pd.category as product_category, 
        ci.age_group,
        sum(sl.quantity * sl.price) as total_sales_amount
    from
            sales as sl
    left join 
            products as pd
    on 
            sl.product_id = pd.product_id
    left join 
            customer_info as ci
    on 
            sl.sale_id = ci.sale_id
    group by 
            pd.category,
            ci.age_group
)a
left join (
    select
        pd.category,
        sum(sl.quantity * sl.price) as category_sum_amount
    from 
            sales as sl
    left join 
            products as pd
    on 
            sl.product_id = pd.product_id
    group by 
            pd.category
)b
on 
        a.product_category = b.category
order by 
        a.product_category,
        purchase_percentage desc