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

with quantity_tb as(
    select product_id,month(order_date) as months,sum(quantity) as month_qantity
    from orders
    group by product_id,month(order_date)
),
age_group_tb as(
    select customer_id,
    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'
    else '61+' end 
    as age_group
    from customers
),
max_customer_tb as(
    select product_id,customer_id
    from(
        select product_id,customer_id,row_number() over(partition by product_id order by customer_quantity desc) as rn
        from(
            select product_id,customer_id,sum(quantity) as customer_quantity
            from orders
            group by product_id,customer_id
        )t
    )t
    where rn=1
),
max_customer_age_group_tb as(
    select max_customer_tb.product_id,age_group_tb.age_group
    from max_customer_tb
    left join age_group_tb 
    on max_customer_tb.customer_id=age_group_tb.customer_id
)
select q.product_id,
sum(q.month_qantity*p.unit_price) as total_sales,
p.unit_price,sum(q.month_qantity) as total_quantity,
round(sum(q.month_qantity*p.unit_price)*1.0/12,2) as avg_monthly_sales,
max(q.month_qantity) as max_monthly_quantity,
m2.age_group as customer_age_group
from quantity_tb as q
left join products as p
on q.product_id=p.product_id
left join max_customer_age_group_tb as m2
on p.product_id=m2.product_id
group by q.product_id,m2.age_group
order by sum(q.month_qantity*p.unit_price) desc,product_id