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

购买量最多的客户年龄段（customer_age_group）
1、每个产品每个用户购买数量
2、根据每个产品每个用户购买数量和用户年龄，统计购买数量降序年龄升序排名
3、排名第一的用户年龄
4、处理年龄区间

select product_id , total_sales , unit_price, 
         total_quantity,
        round(avg_monthly_sales,2) as avg_monthly_sales,
         max_monthly_quantity,
        case when maxq_age>=1 and maxq_age<=10 then '1-10'
        when maxq_age>=11 and maxq_age<=20 then'11-20'
        when maxq_age>=21 and maxq_age<=30 then'21-30'
        when maxq_age>=31 and maxq_age<=40 then'31-40'
        when maxq_age>=41 and maxq_age<=50 then'41-50'
        when maxq_age>=51 and maxq_age<=60 then'51-60'
        when maxq_age>=61 then'61+'
        end as customer_age_group
from (
    select product_id ,sum(sales) as total_sales , max(unit_price ) as unit_price, 
        sum(quantity) aS total_quantity,
        sum(sales)/12 aS avg_monthly_sales,
        max(mon_quantity) as max_monthly_quantity,
		排名第一的用户年龄
        max(case when rn =1 then customer_age end ) as maxq_age
    from (

        select customer_id , product_id ,quantity, sales, order_date,unit_price,customer_age,
            cus_quantity,
            mon_quantity,
			根据每个产品每个用户购买数量和用户年龄，统计购买数量降序年龄升序排名
            row_number()over(partition by product_id order by cus_quantity desc,customer_age) as rn
        from (
            select o.customer_id , o.product_id ,quantity, quantity*p.unit_price as sales, order_date,unit_price,c.customer_age,
			每个产品每个用户购买数量
                sum(quantity)over(partition by o.product_id, o.customer_id) as cus_quantity,
				月购买数量
                sum(quantity)over(partition by o.product_id, substr(order_date,1,7)) as mon_quantity
            from orders o inner join products p on o.product_id= p.product_id
            inner join customers c on o.customer_id= c.customer_id
            where substr(order_date,1,4)='2023'
        ) t 
    ) t
     group by product_id
)t

order by 2 desc ,1