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

select product_id,round(total_quantity*unit_price,2) as total_sales,unit_price,total_quantity,
       round(round(total_quantity*unit_price,2)/12,2) as avg_monthly_sales,
       quantity as max_monthly_quantity,
       customer_age_group
from (
    select  p.product_id,unit_price ,month(order_date)  as month,if(customer_age>1 and  customer_age<10,'1-10',if(customer_age<21,'11-20',if(customer_age<31,'21-30',if(customer_age<41,'31-40',if(customer_age<51,'41-50',if(customer_age<61,'51-60','61+')))))) as customer_age_group,customer_age,
                   dense_rank() over (partition by p.product_id order by quantity desc,customer_age   ) as rk,
                   sum(quantity) over (partition by p.product_id) as total_quantity,
                   quantity
    from orders join  products p on orders.product_id = p.product_id
    join customers c on orders.customer_id = c.customer_id
    group by  p.product_id,  unit_price, month(order_date),customer_age,quantity
     ) as t
where  rk=1
order by total_sales desc