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

with 
t1 as(
    select
        p.category as product_category,
        c.age_group as age_group,
        sum(s.quantity*s.price) as total_group_sales_amount
    from customer_info c 
    left join sales s on c.sale_id=s.sale_id
    left join products p on s.product_id=p.product_id
    group by p.category,c.age_group),
t2 as(
    select
        p.category as product_category,
        sum(s.quantity*s.price) as total_catgotry_sales_amount
    from customer_info c 
    left join sales s on c.sale_id=s.sale_id
    left join products p on s.product_id=p.product_id
    group by p.category)

select 
t1.product_category,
t1.age_group,
t1.total_group_sales_amount as total_sales_amount,
round(t1.total_group_sales_amount/t2.total_catgotry_sales_amount,2) as purchase_percentage
from t1 left join t2 on t1.product_category=t2.product_category
order by t1.product_category,t1.age_group;