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

WITH order_by_product_by_month AS(
    SELECT 
        product_id,
        avg(month_quantity) AS avg_monthly_quantity, --这一步不需要哈，理解错题意了，但是如果有这一步就更难
        max(month_quantity) AS max_monthly_quantity
    FROM (SELECT 
        product_id,
        DATE_FORMAT(order_date,'%Y-%m') AS order_month,
        SUM(quantity) AS month_quantity
    FROM orders 
    GROUP BY product_id,DATE_FORMAT(order_date,'%Y-%m')) AS t1
    GROUP BY product_id
),
order_by_product AS(
    SELECT 
        a.product_id,
        b.unit_price * SUM(a.quantity) AS total_sales,
        b.unit_price,
        SUM(a.quantity) AS total_quantity
    FROM orders AS a
    LEFT JOIN products AS b
    ON a.product_id=b.product_id
    GROUP BY a.product_id,b.unit_price
),
order_by_age AS(
    SELECT 
        t2.product_id,
        CASE 
            WHEN t2.customer_age MOD 10=0 THEN CONCAT(CAST((t2.customer_age DIV 10-1) *10+1 AS CHAR),'-',CAST((t2.customer_age DIV 10)*10 AS CHAR)) 
            ELSE CONCAT(CAST(t2.customer_age DIV 10 *10+1 AS CHAR),'-',CAST((t2.customer_age DIV 10+1)*10 AS CHAR)) 
        END AS customer_age_group
    FROM (SELECT 
        a.product_id,
        a.customer_id,
        c.customer_age,
        rank() over (partition by a.product_id order by a.quantity DESC,c.customer_age) AS rk
    FROM orders AS a
    LEFT JOIN customers AS c
    ON a.customer_id=c.customer_id) AS t2
    WHERE t2.rk=1
)
SELECT 
    a.product_id,
    a.total_sales,
    a.unit_price,
    a.total_quantity,
    round(a.total_sales/12,2) AS avg_monthly_sales,
    b.max_monthly_quantity,
    c.customer_age_group
FROM order_by_product AS a
LEFT JOIN order_by_product_by_month AS b 
ON a.product_id=b.product_id
LEFT JOIN order_by_age AS c
ON a.product_id=c.product_id
ORDER BY a.total_sales DESC;