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

select product_id
      ,total_sales
      ,unit_price
      ,total_quantity
      ,round(avg_monthly_sales,2) as avg_monthly_sales
      ,quantity as max_monthly_quantity
      ,customer_age_group
from (
    select t1.product_id
           ,sum(unit_price*quantity) over(partition by t1.product_id) as total_sales
           ,unit_price
           ,sum(quantity) over(partition by t1.product_id) as total_quantity
           ,month(order_date)
           ,quantity
           ,rank() over(partition by t1.product_id order by quantity desc,customer_age asc) as monthly_quantity_rk
           ,sum(unit_price*quantity/12) over(partition by t1.product_id) as avg_monthly_sales
           ,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 customer_age_group
    from orders t1  left join products t2  on t1.product_id = t2.product_id
    left join customers t3  on t1.customer_id = t3.customer_id) t4
where monthly_quantity_rk = 1
order by total_sales desc,product_id