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

with t1 as(
select product_id
,sum(quantity) total_quantity
from orders
left join customers
on orders.customer_id = customers.customer_id
group by 1
)

,t2 as (
select
t1.product_id
,unit_price
,sum(total_quantity*unit_price)  total_sales
,round(sum(total_quantity*unit_price)/12,2) avg_monthly_sales
from t1
left join products p
on t1.product_id = p.product_id
group by 1,2
order by 3 desc )

, t3 as(
select product_id ,max_monthly_quantity from 
(select product_id ,month(order_date) ,sum(quantity) max_monthly_quantity
, rank () over(partition by product_id   order by sum(quantity) desc ) ranking
from orders
group by 1,2)rk
where ranking = 1)

, t4 as (
select product_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
customer_age_group
 from 
(select product_id ,orders.customer_id ,customer_age
,sum(quantity) max_monthly_quantity
, row_number () over(partition by product_id   order by sum(quantity) desc ) ranking
from orders
left join customers
on orders.customer_id = customers.customer_id
group by 1,2,3)rk
where ranking = 1
)

select t1.product_id 
,total_sales
,unit_price
,total_quantity
,avg_monthly_sales
,max_monthly_quantity
,customer_age_group
from t1 
left join t2 on t1.product_id = t2.product_id
left join t3 on t1.product_id = t3.product_id
left join t4 on t1.product_id = t4.product_id
group by 1,2,3,4,5,6,7
order by 2 desc