题解 | 电商平台需要对商品的销售和评价情况进行综合分析

WITH avg_product AS(
    SELECT
        p.product_id,
        p.product_name,
        ROUND(AVG(r.rating),2) AS average_rating
    FROM
        products_underline AS p
        INNER JOIN
        sales_underline AS s ON p.product_id=s.product_id
        INNER JOIN
        reviews_underline AS r ON s.product_id=r.product_id
    GROUP BY
        p.product_id,
        p.product_name
    HAVING
        average_rating<4
    )
SELECT
    a.product_id,
    a.product_name,
    SUM(s.quantity) AS total_quantity,
    a.average_rating
FROM
    avg_product AS a
    INNER JOIN
    sales_underline  AS s ON a.product_id=s.product_id
GROUP BY
    a.product_id,
    a.product_name
ORDER BY
    a.average_rating,
    a.product_id