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

with
    temp1 as (
        select
            product_id,
            round(avg(rating), 2) as average_rating
        from
            reviews_underline
        group by
            product_id
        having
            avg(rating) < 4
    ),
    temp2 as (
        select
            p.product_id,
            p.product_name,
            sum(quantity) as total_quantity
        from
            products_underline p
            join (
                select
                    *
                from
                    sales_underline
                where
                    sale_date between '2024-01-01' and '2024-12-31'
            ) s using (product_id)
        group by
            p.product_id,
            p.product_name
    )
select
    product_id,
    product_name,
    total_quantity,
    average_rating
from
    temp1
    join temp2 using (product_id)
order by
    average_rating,
    product_id