题解 | 电商平台想要了解不同商品在不同月份的销售趋势

with t as (
    select a.product_id, product_name,
    quantity as quantity,
    right(sale_month,2) as month
    from products_underline a
    join sales_underline b on a.product_id=b.product_id
    where left(sale_month,4)=2024 and right(sale_month,2) in (1, 2, 3, 4, 5, 6)
)
select DISTINCT product_id, product_name,
       sum(monthly_quantity) over (partition by product_id) as total_sales,
       max(monthly_quantity) over (partition by product_id) as max_monthly_sales,
       min(monthly_quantity) over (partition by product_id) as min_monthly_sales,
       round(avg(monthly_quantity) over (partition by product_id)) as avg_monthly_sales
from
(
    select product_id, product_name, month, sum(quantity) as monthly_quantity
    from t
    group by product_id, product_name, month
) temp
order by product_id