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

神人需求

with q5 as (
select o.product_id,
round(sum(quantity)*unit_price,2) as total_sales,
unit_price,
round(sum(quantity),2) as total_quantity,
round(sum(quantity)*unit_price/12,2) as avg_monthly_sales from orders as o
left join products as p on o.product_id = p.product_id
where year(order_date) = 2023
group by o.product_id
),
q61 as (
select product_id,
max(quantity) as max_quantity,
month(order_date) as month from orders
where year(order_date) = 2023
group by product_id,month
),
q62 as (
select product_id,
max(max_quantity) as max_monthly_quantity from q61
group by product_id
),
q7 as (
select product_id,o.customer_id,sum(quantity) as total,
row_number()over(partition by product_id order by sum(quantity) desc,customer_age) quantity_rk,
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,
customer_age from orders as o
left join customers as c on c.customer_id = o.customer_id
where year(order_date) = 2023
group by product_id,o.customer_id
)
select 
q5.product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,max_monthly_quantity,customer_age_group
from q5
    join q62 on q5.product_id=q62.product_id
    join q7 on q5.product_id=q7.product_id
where quantity_rk = 1
order by total_sales desc,product_id