题解 | 对商品的销售情况进行深度分析

WITH t1 AS (
    SELECT category,SUM(quantity*price) AS total_sales_amount
    FROM products p 
    LEFT JOIN sales s ON p.product_id=s.product_id
    LEFT JOIN customer_info  c ON s.sale_id=c.sale_id 
    GROUP BY category
),
t2 AS (
    SELECT category,age_group,SUM(quantity*price) AS total_sales_amount
    FROM products p 
    LEFT JOIN sales s ON p.product_id=s.product_id
    LEFT JOIN customer_info  c ON s.sale_id=c.sale_id 
    GROUP BY category,age_group
)
SELECT t1.category AS product_category,
       t2.age_group,
       t2.total_sales_amount,
       ROUND(t2.total_sales_amount/t1.total_sales_amount,2) AS purchase_percentage
FROM t1 
LEFT JOIN t2 ON t1.category=t2.category
ORDER BY t1.category ASC ,total_sales_amount DESC