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

select
a.product_id,
b.product_name,
sum(a.quantity) as total_quantity,
round(avg(c.avg_rating),2) as average_rating
from sales_underline a join products_underline b on a.product_id=b.product_id join (select product_id,avg(rating) as avg_rating from  reviews_underline group by product_id) c on a.product_id=c.product_id
where year(a.sale_date)=2024 and c.avg_rating<=4
group by a.product_id,b.product_name
order by average_rating,a.product_id