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

with a as(select product_id,
sum(quantity*unit_price) total_sales,
unit_price,sum(quantity) total_quantity,
round(sum(quantity*unit_price)/12,2) avg_monthly_sales,
max(quantity) max_monthly_quantity
from  orders
join  products using(product_id)
group by product_id),
b as (
    select 
    product_id,rank()over(partition by product_id order by sum(quantity) desc,customer_age) rk,
    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 '61+' end as customer_age_group
    from 
    orders
    join customers using(customer_id)
    group by product_id,customer_id),
c as(
    select product_id,customer_age_group
    from b
    where rk =1),
d as(
    select 
    product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,max_monthly_quantity,
    customer_age_group
    from c
    join a using(product_id)
    order by total_sales desc,product_id)
select *
from d