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

WITH t AS (
    SELECT p.product_id,SUM(quantity*unit_price) AS total_sales,
           SUM(quantity) AS total_quantity,
           SUM(quantity*unit_price)/12  AS avg_monthly_sales,unit_price
    FROM orders o
    LEFT JOIN products p ON o.product_id=p.product_id
    GROUP BY p.product_id
),
t1 AS (
    SELECT p.product_id,SUM(quantity) AS ms,MONTH(order_date) AS m
    FROM orders o
    LEFT JOIN products p ON o.product_id=p.product_id
    GROUP BY p.product_id, m
),
t4 AS (
  SELECT product_id , MAX(ms) AS max_monthly_quantity
  FROM t1
  GROUP BY product_id 
),
t2 AS (
    SELECT 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,
                o.product_id,SUM(quantity) AS max_quantity
    FROM customers c
    LEFT JOIN  orders o   ON  c.customer_id=o.customer_id
    GROUP BY o.product_id,customer_age_group
),
t3 AS (
    SELECT product_id,
           customer_age_group,
           max_quantity,
           RANK() OVER(PARTITION BY product_id ORDER BY max_quantity DESC ,customer_age_group ASC )  AS rnk
    FROM t2 
)
SELECT  t.product_id,
        total_sales,
        unit_price,
        total_quantity,
        ROUND(avg_monthly_sales,2) AS avg_monthly_sales,
        max_monthly_quantity,
        customer_age_group
FROM t 
LEFT JOIN t4 ON t.product_id=t4.product_id
LEFT JOIN t3 ON t.product_id=t3.product_id
WHERE rnk<=1
ORDER BY total_sales DESC,t.product_id ASC