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

#查询出所有产品中，2024年平均产品评分低于4分的所有产品ID\产品名称\总销量。查询结果按照产品平均分升序排列，如果平均分一致按照产品ID升序排列。
select t1.product_id,product_name,total_quantity,average_rating
from(
    select a.product_id,product_name,sum(quantity) as total_quantity
    from sales_underline as a
    inner join products_underline as b
    on a.product_id = b.product_id
    where year(sale_date) = '2024'
    group by a.product_id,product_name
) as t1
inner join (
    select product_id,round(avg(rating),2) as average_rating
    from reviews_underline
    group by product_id
) as t2
on t1.product_id = t2.product_id
where average_rating < 4
order by average_rating,t1.product_id