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

with
    t1 as (
        select
            product_id,
            round(avg(rating), 2) as average_rating
        from
            reviews_underline
        group by
            product_id
        having
            average_rating < 4
    )
select
    product_id,
    product_name,
    sum(quantity) as total_quantity,
    average_rating
from
    products_underline
    join sales_underline using (product_id)
    join t1 using (product_id)
where
    year(sale_date) = 2024
group by
    product_id,
    product_name
order by
    average_rating asc,
    product_id asc;