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

# 筛选出2024年平均产品评分低于4分的所有产品ID、名称、总销量

with
t1 as(
    select
        product_id,
        product_name,
        sum(quantity) as total_quantity
    from
        sales_underline
        left join products_underline using(product_id)
    group by
        product_id
)
,
t2 as(
    select
        product_id,
        round(avg(rating),2) as average_rating
    from
        reviews_underline
    group by
        product_id
)
,
t3 as(
    select
        product_id,
        product_name,
        total_quantity,
        average_rating
    from
        t2 left join t1 using(product_id)
    where
        average_rating<4
    order by
        average_rating,
        product_id
)

select * from t3