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

# 每个商品 不同年龄 性别 销售总额 不同年龄群体比例
with
t1 as(
    select
        customer_id,
        product_name,
        category,
        quantity,
        price,
        age_group
    from
        sales
        left join products using(product_id)
        left join customer_info using(sale_id)
)
,
t2 as(
    select
        category,
        age_group,
        sum(quantity*price) as total_sales_amount
    from
        t1
    group by
        category,
        age_group
)
,
t3 as(
    select
        category as product_category,
        age_group,
        total_sales_amount,
        round(total_sales_amount/sum(total_sales_amount)over(partition by category),2) as purchase_percentage
    from
        t2
)

select * from t3 order by product_category,total_sales_amount desc