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

with t as(
    select
        p.product_id,
        p.product_name,
        s.sale_month,
        s.quantity as monthly_sales,
        sum(s.quantity) over(partition by p.product_id) as total_sales 
    from sales_underline s left join products_underline p
    on s.product_id=p.product_id
    where s.sale_month between '2024-01' and '2024-06')

select 
    t.product_id,
    t.product_name,
    t.total_sales,
    round(max(t.monthly_sales),0) as max_monthly_sales,
    round(min(t.monthly_sales),0) as min_monthly_sales,
    round(avg(t.monthly_sales),0) as avg_monthly_sales
from 
    t
group by 
    t.product_id,
    t.product_name,
    t.total_sales
order by
    t.product_id