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

with t3 as ( # 计算基础指标
        select product_id
        , round(sum(unit_price*quantity),2) total_sales
        , round(max(unit_price),2) unit_price
        , sum(quantity) total_quantity
        , round(sum(unit_price*quantity)/12,2) avg_monthly_sales
        from orders
        left join products using(product_id)
        where year(order_date)='2023'
        group by product_id
),
t2 as ( # 计算单月最大销量
        select product_id
        ,max(monthly_quantity) max_monthly_quantity
        from (
            select product_id
            ,month(order_date)
            ,sum(quantity) monthly_quantity
            from orders
            group by product_id, month(order_date) 
        ) tb1
        group by product_id
),
t1 as ( # 计算购买量最多的客户年龄段
        select product_id
        ,customer_age_group
        from (
                select product_id
                ,customer_age_group
                ,sum_quantity
                ,row_number()over(partition by product_id order by sum_quantity desc,customer_age_group) rk
                from (
                        select product_id
                        ,customer_age_group
                        ,sum(quantity) sum_quantity
                        from ( # 所需字段
                                select product_id
                                , customer_age
                                , quantity
                                , case when customer_age<=10 then '1-10'
                                        when customer_age<=20 then '11-20'
                                        when customer_age<=30 then '21-30'
                                        when customer_age<=40 then '31-40'
                                        when customer_age<=50 then '41-50'
                                        when customer_age<=60 then '51-60'
                                        else '60+' end customer_age_group
                                from orders
                                left join customers using(customer_id)
                                where year(order_date)='2023'
                        ) tb 
                        group by product_id,customer_age_group
                ) tb2
        ) tb3 
        where rk=1
)
select product_id
        , total_sales
        , unit_price
        , total_quantity
        , avg_monthly_sales
        , max_monthly_quantity
        , customer_age_group
from t3
left join t2 using(product_id)
left join t1 using(product_id)
order by total_sales desc,product_id