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

with
    a as (
        select
            o.product_id,
            case
                when c.customer_age >= 1
                and c.customer_age <= 10 then '1-10'
                when c.customer_age >= 11
                and c.customer_age <= 20 then '11-20'
                when c.customer_age >= 21
                and c.customer_age <= 30 then '21-30'
                when c.customer_age >= 31
                and c.customer_age <= 40 then '31-40'
                when c.customer_age >= 41
                and c.customer_age <= 50 then '41-50'
                when c.customer_age >= 51
                and c.customer_age <= 60 then '51-60'
                else '61+'
            end as customer_age_group,
            sum(o.quantity),
            row_number() over (
                partition by
                    product_id
                order by
                    sum(o.quantity) desc
            ) as number_order
        from
            orders o
            left join customers c on c.customer_id = o.customer_id
        where
            year(o.order_date) = 2023
        group by
            o.product_id,
            customer_age_group
    ),
    b as (
        select
            o.product_id,
            sum(o.quantity) quantity,
            avg(p.unit_price) unit_price,
            month(o.order_date)
        from
            orders o
            left join products p on p.product_id = o.product_id
        where
            year(o.order_date) = 2023
        group by
            o.product_id,month(o.order_date)
    )
select
    a.product_id,
    round(sum(b.quantity*b.unit_price),2) as total_sales,
    round(avg(b.unit_price),2) as unit_price,
    sum(b.quantity) as total_quantity,
    round(sum(b.quantity*b.unit_price)/12,2) as avg_monthly_sales,
    max(b.quantity) as max_monthly_quantity,
    customer_age_group
from
    a
left join 
    b 
on
    a.product_id = b.product_id
where
    a.number_order = 1
group by
    a.product_id,customer_age_group
order by
    total_sales desc,product_id;

不知道我的想法会不会比较累赘，第一个表先是算出各个产品购买量最多的用户年龄段，第二个表是把要计算的指标列求出来（我这里带上月份是为了后续好计算2023年各个月份里购买次数最多的数据），最后根据用户id把两个表拼起来，再计算结果数据