select product_id,round(sum(quantity)*unit_price,2) total_sales,
unit_price,sum(quantity) total_quantity,
round(sum(quantity)*unit_price/12,2) avg_monthly_sales,
max_monthly_quantity,customer_age_group
from products p
join orders o using(product_id)
join
(select product_id,max(month_qty) max_monthly_quantity
from
(select product_id,sum(quantity) month_qty
from orders
group by product_id,month(order_date)) m1
group by product_id) m2 using(product_id)
join
(select product_id,customer_age_group
from
(select product_id,customer_id, max(cus_qty) max_qty,customer_age,
if(customer_age>60,'61+',if(customer_age>50,'51-60',if(customer_age>40,'41-50',
if(customer_age>30,'31-40',if(customer_age>20,'21-30',if(customer_age>10,'11-20','1-10')))))) customer_age_group,
row_number()over(partition by product_id order by max(cus_qty) desc,customer_age) rk
from
(select product_id,customer_id,sum(quantity) cus_qty
from orders
group by product_id,customer_id) n1
join customers c using(customer_id)
group by product_id,customer_id,customer_age
order by product_id,customer_id,customer_age) n2
where rk=1) n3
using(product_id)
group by product_id,unit_price,max_monthly_quantity,customer_age_group
order by total_sales desc,product_id;
抽丝剥茧，一步步展开。细节的需求要写清楚。我主要错了4个地方：少了1个逗号；窗口函数那里没有分组，排序也写错列名了；总销售额round后面忘记写小数点数量了；平均月销的分母错写成总销量了应是总销售额。其他没啥问题。