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

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

小题一道