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

with 
t1 as
(
    select
        p.product_id,
        p.product_name,
        sum(s.quantity) as total_sales,
        round(sum(s.quantity) / count(distinct sale_month), 0) as avg_monthly_sales
    from 
        products_underline p 
    join 
        sales_underline s 
    on 
        p.product_id = s.product_id
    where
        s.sale_month between '2024-01' and '2024-06'
    group by
        p.product_id,
        p.product_name
),
t2 as
(
    select
        sale_id,
        product_id,
        sale_month,
        quantity,
        rank() over (partition by product_id order by quantity desc) rk1,
        rank() over (partition by product_id order by quantity ) rk2
    from 
        sales_underline
    where
        sale_month between '2024-01' and '2024-06'
),
t3 as
(
    select
        product_id,
        quantity as max_monthly_sales
    from t2
    where rk1 = 1
),
t4 as
(
    select
        product_id,
        quantity as min_monthly_sales
    from t2
    where rk2 = 1
)
select
    t1.product_id,
    t1.product_name,
    t1.total_sales,
    max_monthly_sales,
    min_monthly_sales,
    avg_monthly_sales
from 
    t1 
join 
    t3
on 
    t1.product_id = t3.product_id
join 
    t4
on 
    t1.product_id = t4.product_id

感觉写的太长了，大佬有更简单的写法的话求分享