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

with a as(
select 
    o.product_id,
    round(sum(o.quantity*p.unit_price),2)  total_sales,
    p.unit_price,
    sum(o.quantity)  total_quantity,
    round(sum(o.quantity*p.unit_price)/12,2)   avg_monthly_sales
from orders o
left join products p 
on o.product_id = p.product_id
group by o.product_id,p.unit_price
), b as 
(
	select
		c.customer_id,
		o.product_id,
		sum(o.quantity) max_num,
		month(o.order_date)  date,
		rank() over (partition by o.product_id order by sum(o.quantity) desc) rk,
		case 
        when c.customer_age between 1 and 10 then '1-10'
        when c.customer_age between 11 and 20 then '11-20'
        when c.customer_age between 21 and 30 then '21-30'
        when c.customer_age between 31 and 40 then '31-40'
        when c.customer_age between 41 and 50 then '41-50'
        when c.customer_age between 51 and 60 then '51-60'
        else '61+'
        end  customer_age_group
	from customers c
	left join orders o
	on c.customer_id = o.customer_id
	where year(o.order_date) = '2023'
	group by 
		c.customer_id,
		o.product_id,
		month(o.order_date),
		c.customer_age
), c as (
select
	a.product_id,
	a.total_sales,
	a.unit_price,
	a.total_quantity,
	a.avg_monthly_sales,
	b.max_num  max_monthly_quantity,
	customer_age_group,
	dense_rank() over (partition by a.product_id order by b.customer_age_group asc) rn
	from a
	left join b
	on a.product_id = b.product_id
	where rk = 1
)
select
	product_id,
	total_sales,
	unit_price,
	total_quantity,
	avg_monthly_sales,
	max_monthly_quantity,
	customer_age_group
from c
where rn = 1 
order by total_sales desc,product_id

这题怎么说呢,又臭又长