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

with t1 as(
    select
    product_id,
    sum(quantity)  max_monthly_quantity,
    row_number() over (
        partition by
            product_id
        order by
            sum(quantity) desc
    ) rn1
from
    orders
where
    year (order_date) = 2023
group by
    product_id,
    month (order_date)
),
    t2 as (
        select
            product_id,
            a.customer_id,
            case
                when customer_age between 0 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'
                else '61+'
            end customer_age_group,
            row_number() over (
                partition by
                    product_id
                order by
                    sum(quantity) desc,
                    customer_age asc
            ) rn2
        from
            orders a
            inner join customers b on a.customer_id = b.customer_id
        group by
            product_id,
            a.customer_id
    ),
t3 as(select
a.product_id,
unit_price*sum(quantity) total_sales,
unit_price,
sum(quantity) total_quantity,
round((unit_price*sum(quantity))/12,2) avg_monthly_sales
from
orders a inner join products b on a.product_id=b.product_id
group by a.product_id),
t4 as(select
product_id,
max_monthly_quantity
from 
t1
where rn1=1),
t5 as(
   select
product_id,
customer_age_group
from
t2
where rn2=1 
)

select
t3.product_id product_id,
total_sales,
unit_price,
total_quantity,
avg_monthly_sales,
max_monthly_quantity,
customer_age_group
from
t3 inner join t4 on t3.product_id=t4.product_id
inner join t5 on t3.product_id=t5.product_id
order by total_sales desc,product_id asc