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

with t1 as (
    select product_id,round(avg(rating),2) average_rating
    from reviews_underline r 
    group by product_id 
    having average_rating < 4
)
select t1.product_id,product_name,sum(quantity) total_quantity,average_rating
from t1 join products_underline p on t1.product_id = p.product_id
join sales_underline s on t1.product_id = s.product_id and year(sale_date) = 2024
group by t1.product_id
order by average_rating,t1.product_id