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

select distinct p.product_id
,product_name
,total_quantity
,average_rating
from products_underline p
join (
    select product_id
    ,sum(coalesce(quantity,0)) as total_quantity
    from sales_underline
    where substring(sale_date,1,4) = "2024"
    group by product_id
) s on p.product_id = s.product_id
join (
    select product_id
    ,round(avg(coalesce(rating,0)),2) as average_rating
    from reviews_underline
    group by product_id
) r on p.product_id = r.product_id
where average_rating < 4
order by average_rating
,product_id