/*
--t1筛选出前5列，t2先按product_id，month(order_date)统计出各商品的最大月销售量
--t3使用窗口函数ROW_NUMBER按最大月销量进行DESC排序，便于最后挑选出最大月销量
--t4先按product_id，customer_id统计出各商品的每个客户的年购买量
--t5使用窗口函数ROW_NUMBER按各商品的每个客户的年购买量进行DESC排序，便于最后挑选出购买量最大客户的年龄段
--最后汇总
*/
with t1 as(
    SELECT o.product_id,SUM(quantity)*unit_price as total_sales,unit_price,SUM(quantity) as total_quantity,ROUND(SUM(quantity)*unit_price/12,2) as avg_monthly_sales FROM orders o INNER JOIN products p ON 
    o.product_id=p.product_id GROUP BY o.product_id ORDER BY total_sales DESC
),
t2 as(
    SELECT product_id,MONTH(order_date) as mt, SUM(quantity) as  max_monthly_quantity FROM orders GROUP BY product_id,MONTH(order_date) ORDER BY product_id
),
t3 as(
SELECT product_id,max_monthly_quantity,ROW_NUMBER() OVER(partition by product_id ORDER BY max_monthly_quantity DESC) as num FROM t2
),
t4 as(
    SELECT product_id,customer_id,SUM(quantity) as max_c_quantity FROM orders GROUP BY product_id,customer_id ORDER BY product_id,max_c_quantity DESC
),
t5 as(
    SELECT product_id,t4.customer_id,customer_age,ROW_NUMBER() OVER(partition by product_id ORDER BY max_c_quantity DESC,customer_age) as num FROM t4 
    INNER JOIN customers as c ON t4.customer_id=c.customer_id
)
SELECT t1.product_id,t1.total_sales,t1.unit_price,t1.total_quantity,t1.avg_monthly_sales,t3.max_monthly_quantity,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 t1
INNER JOIN t3 ON t1.product_id=t3.product_id
INNER JOIN t5 ON t1.product_id=t5.product_id WHERE t3.num<=1 AND t5.num<=1
ORDER BY t1.total_sales DESC,t1.product_id