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

with t1 as (select p.product_id,category,sale_id,quantity,price from products p
join sales s
on p.product_id = s.product_id),
t2 as 
(
select c.sale_id,age_group,category,quantity,price from customer_info c
join t1 
on c.sale_id = t1.sale_id
),
t3 as
(
select category,sum(quantity*price) as sum_sales from t2
group by category
),
t4 as(
select category,age_group,sum(price*quantity) as part_sales from t2
group by category,age_group)

select t4.category as product_category,age_group,part_sales as total_sales_amount,round(part_sales/sum_sales,2) as purchase_percentage from t4
join t3
on t4.category = t3.category
order by t4.category,age_group