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

with t1 as
(
select
    category product_category,
    age_group,
    round(sum(quantity*price),2) total_sales_amount
from
    products p
    join sales s on p.product_id = s.product_id
    join customer_info c on s.sale_id = c.sale_id
group by 
    category,
    age_group
),t2 as
(
select
    category product_category,
    round(sum(quantity*price),2) total_sales_amount
from
    products p
    join sales s on p.product_id = s.product_id
    join customer_info c on s.sale_id = c.sale_id
group by 
    category
)
select
    t1.product_category,
    age_group,
    t1.total_sales_amount,
    round(t1.total_sales_amount/t2.total_sales_amount,2) purchase_percentage
from
    t1 join t2 on t1.product_category = t2.product_category
order by
    t1.product_category,
    purchase_percentage desc