题解 | 统计每个产品的销售情况

-- 统计不同年龄段购买该产品的数量   使用窗口函数进行统计
with a as (
    select 
    quantity_sum
    ,product_id
    ,customer_age_group
    from 
    (
    select 
    sum(quantity) as quantity_sum
    ,product_id
    ,customer_age_group
    ,rank() over(partition by product_id order by sum(quantity)  desc, customer_age_group asc ) as rk
    from 
    (
        select 
        t1.product_id
        ,t1.quantity
        ,date_format(t1.order_date,'%Y-%m') as order_month
        ,case when t2.customer_age between 1 and 10  then '1-10'
            when t2.customer_age between 11 and 20  then '11-20'
            when t2.customer_age between 21 and 30  then '21-30'
            when t2.customer_age between 31 and 40  then '31-40'
            when t2.customer_age between 41 and 50  then '41-50'
            when t2.customer_age between 51 and 60  then '51-60'
            else '61+'
        end  as customer_age_group
        from orders t1 
        left join customers t2
        on t1.customer_id = t2.customer_id
        where year(t1.order_date) = 2023
    ) t3
    group by product_id,customer_age_group
    ) t4
    where rk = 1
),

-- 统计 单月最高销量
b as (
    select 
        order_date_month
        ,quantity_month
        ,product_id
        ,rk
    from 
    (
        select 
        order_date_month
        ,quantity_month
        ,product_id
        ,rank() over(partition by product_id order by quantity_month desc ) as rk
        from
        (
            select 
            date_format(order_date,'%Y-%m') as order_date_month
            ,sum(quantity) as quantity_month
            ,product_id
            from orders 
            group by product_id,order_date_month
        ) t1
    ) t2 
    where rk = 1
),

-- 产品ID（product_id）
-- 总销售额（total_sales）
-- 单价（unit_price）
-- 总销量（total_quantity）
-- 月平均销售额（avg_monthly_sales）
c as (
    select 
    t2.product_id
    ,sum(t1.quantity) as total_quantity
    ,t2.unit_price
    ,sum(t1.quantity) * t2.unit_price as total_sales
    ,round(sum(t1.quantity) * t2.unit_price /12,2)  as avg_monthly_sales
    from orders t1
    left join products t2 
    on t1.product_id = t2.product_id
    left join customers t3
    on t1.customer_id = t3.customer_id
    where year(t1.order_date) = 2023
    group by t2.product_id,t2.unit_price
)

select 
distinct
c.product_id
,c.total_sales
,c.unit_price
,c.total_quantity
,c.avg_monthly_sales
,b.quantity_month as max_monthly_quantity
,a.customer_age_group
from c 
inner join  b
on c.product_id = b.product_id
inner join a 
on c.product_id = a.product_id
order by c.total_sales desc ,product_id asc