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

WITH t1 AS(    
    SELECT product_id,
    round(sum(quantity*unit_price),2) AS total_sales,
    unit_price,
    sum(quantity) AS total_quantity,
    round(sum(unit_price*quantity)/12,2) AS avg_monthly_sales,
    max(quantity) AS max_monthly_quantity
    FROM(   
        SELECT a.customer_id,a.product_id,a.quantity,month(a.order_date) as month,b.unit_price,c.customer_age
        FROM orders AS a
        JOIN products AS b ON a.product_id = b.product_id 
        JOIN customers AS c ON a.customer_id = c.customer_id
    ) AS d 
    GROUP BY product_id
),
t2 AS(   
    SELECT product_id,customer_age 
    FROM(  
        SELECT *,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY total_quantity DESC,customer_age ASC) AS rk
        FROM(    
            SELECT m.customer_id,m.product_id,sum(m.quantity) total_quantity,n.customer_age
            FROM orders AS m
            JOIN customers as n ON m.customer_id = n.customer_id 
            GROUP BY customer_id,product_id
        ) AS o
    ) AS p 
    WHERE rk = 1
)

SELECT t1.*,
(
    CASE 
        WHEN t2.customer_age >= 1 AND t2.customer_age <= 10 THEN '1-10'
        WHEN t2.customer_age >= 11 AND t2.customer_age <= 20 THEN '11-20'
        WHEN t2.customer_age >= 21 AND t2.customer_age <= 30 THEN '21-30'
        WHEN t2.customer_age >= 31 AND t2.customer_age <= 40 THEN '31-40'
        WHEN t2.customer_age >= 41 AND t2.customer_age <= 50 THEN '41-50'
        WHEN t2.customer_age >= 51 AND t2.customer_age <= 60 THEN '51-60'
        WHEN t2.customer_age >= 61  THEN '61+'
        END
) AS customer_age_group
FROM t1 
JOIN t2 ON t1.product_id = t2.product_id
ORDER BY total_sales DESC,product_id ASC

噫，骇死我哩，竟然写了这么长，其实最终结果表的前面部分用临时表t1就解决了。但是最后一列customer_age_group突然发病跟前面的表基本没关系(估计是出题人有意而为之),所以把前面部分和最后一列当作两部分去处理就好了，我的临时表t2就是专门处理最后一列的，t1表的结果就是结果表除了最后一列的部分，t2表的结果是product_id加上customer_age(用GROUP BY + ROW_NUMBER得到购买总数量最多的),然后t1,t2表一连接，用CASE WHEN语句将年龄转化为分组就好了(终于写完了...)