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

select
    p.product_id as product_id,
    product_name,
    sum(quantity) as total_quantity,
    round(average_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 (
        select
            product_id,
            avg(rating) as average_rating
        from
            reviews_underline
        group by
            product_id
    ) as r on p.product_id = r.product_id
where
    substr(sale_date, 1, 4) = '2024'
group by
    p.product_id
having
    average_rating < 4
order by
    average_rating asc,
    product_id asc;