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

 -- 需要求出每个产品的销售总量、每个产品的购买量（客户ID，购买量、年龄）
with
    product_sale as ( -- 该表输出商品ID，总销售额，单价，月平均销售额
        select
            p.product_id,
            sum(unit_price * quantity) as total_sales,
            p.unit_price,
            sum(quantity) as total_quantity,
            round(sum(quantity * unit_price) / 12, 2) as avg_monthly_sales
        from
            orders as o
            inner join products as p on o.product_id = p.product_id
            inner join customers as c on o.customer_id = c.customer_id
        group by
            p.product_id
        order by
            product_id
    ),
    customer_count as ( -- 该表输出商品ID，顾客年龄，每个商品的各个顾客贡献排名
        select
            o.product_id,
            row_number() over (
                partition by
                    o.product_id
                order by
                    sum(quantity) desc,
                    c.customer_age
            ) as rk,
            c.customer_age
        from
            orders as o
            inner join products as p on o.product_id = p.product_id
            inner join customers as c on o.customer_id = c.customer_id
        group by
            o.product_id,
            o.customer_id
        order by
            o.product_id,
            rk
    ),
    month_sale as ( -- 商品ID，月份，各个月销售量，各个月销售量排名
        select
            product_id,
            month (order_date),
            sum(quantity) as month_sale,
            rank() over (
                partition by
                    o.product_id
                order by
                    sum(quantity) desc
            ) as rk
        from
            orders as o
        group by
            product_id,
            month (order_date)
        order by
            product_id,
            rk
    )
select distinct
    ps.*,
    ms.month_sale as max_monthly_quantity,
    case
        when customer_age between 1 and 10  then '1-10'
        when customer_age between 11 and 20  then '11-20'
        when customer_age between 21 and 30  then '21-30'
        when customer_age between 31 and 40  then '31-40'
        when customer_age between 41 and 50  then '41-50'
        when customer_age between 51 and 60  then '51-60'
        when customer_age > 60 then '61+'
        else 'age error!'
    end as customer_age_group
from
    product_sale as ps
    inner join month_sale as ms on ps.product_id = ms.product_id
    inner join customer_count as cc on ps.product_id = cc.product_id
where
    ms.rk = 1
    and cc.rk = 1
order by
    ps.total_sales desc,
    ps.product_id asc