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

select a.product_id,product_name, round(sum(quantity)/count(distinct review_id),0) total_quantity,round(avg(rating),2)  average_rating
from products_underline a left join (select * from sales_underline  where year(sale_date)=2024 ) b on a.product_id=b.product_id left join reviews_underline c on a.product_id=c.product_id
group by 1,2
having avg(rating)<=4
order by average_rating,product_id