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

WITH
    t AS (
        SELECT
            c.category AS product_category,
            b.age_group,
            sum(a.quantity * a.price) AS total_sales_amount
        FROM
            sales AS a
            JOIN customer_info AS b ON a.sale_id = b.sale_id
            JOIN products AS c ON a.product_id = c.product_id
        GROUP BY
            product_category,
            age_group
    )
SELECT
    *,
    ROUND(
        total_sales_amount / (
            sum(total_sales_amount) OVER (
                PARTITION BY
                    product_category
            )
        ),
        2
    ) AS purchase_percentage
FROM
    t
ORDER BY
    product_category ASC,
    total_sales_amount DESC

表连结+GROUP BY + 聚合函数,看着题目给了一大堆列名和表挺吓人，实际只要找出有用的列进行连接统计就好了,每个表完成自己的任务在拼接到一起就是最终的结果