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

with
    temp0 as (
        select
            category as product_category,
            age_group,
            sum(price * quantity) as total_sales_amount
        from
            products p
            inner join sales s on p.product_id = s.product_id
            inner join customer_info ci on ci.sale_id = s.sale_id
        group by
            category,
            age_group
    ),
    temp1 as (
        select
            product_category,
            sum(total_sales_amount) as total_amount
        from
            temp0 t0
        group by
            product_category
    )
select
    t0.product_category,
    age_group,
    total_sales_amount,
    round(total_sales_amount / total_amount, 2) as purchase_percentage
from
    temp0 t0
    inner join temp1 t1 on t0.product_category = t1.product_category
order by
    t0.product_category asc,
    total_sales_amount desc;