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

select
    t3.product_id product_id,
    t3.product_name product_name,
    total_quantity,
    average_rating
from(
    select product_id, sum(quantity) total_quantity
    from sales_underline
    where sale_date between '2024-01-01' and '2024-12-31'
    group by product_id
) t1
join
(
    select product_id, round(avg(rating),2) average_rating
    from reviews_underline
    group by product_id
) t2
on t1.product_id = t2.product_id
join products_underline t3 on t1.product_id = t3.product_id
where average_rating < 4
group by average_rating, t3.product_id