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

with
    s AS (
        SELECT
            product_id,
            SUM(quantity) AS total_quantity
        FROM
            sales_underline
        WHERE
            YEAR(sale_date) = 2024
        GROUP BY
            product_id
    ),

    r AS (
        SELECT
            product_id,
            ROUND(AVG(rating), 2) AS average_rating
        FROM
            reviews_underline
        GROUP BY
            product_id
        HAVING
            ROUND(AVG(rating), 2) < 4
    )
SELECT
    pu.product_id,
    pu.product_name,
    s.total_quantity,
    r.average_rating
FROM
    products_underline pu
    INNER JOIN s ON pu.product_id = s.product_id
    INNER JOIN r ON pu.product_id = r.product_id
ORDER BY
    r.average_rating ASC,
    pu.product_id ASC;