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

with
    total as (
        select
            category,
            sum(price * quantity) as total_price
        from
            products p
            join sales s on p.product_id = s.product_id
        group by
            category
    )

先建立一个临时表，计算不同品类的总销售额，然后合并其余的表。

select
    p.category as product_category,
    c.age_group,
    sum(s.price * s.quantity) as total_sales_amount,
    round(sum(s.price * s.quantity) / t.total_price, 2) as purchase_percentage
from
    products p
    join sales s on p.product_id = s.product_id
    join customer_info c on s.sale_id = c.sale_id
    join total t on t.category = p.category
group by
    p.category,
    c.age_group
order by
    p.category asc;

这里合并了四个表，包括建立的临时表，所以也就有了各品类的总销售额，需要groupby 商品种类和年龄组，然后再做相应计算。