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

with t1 as (
select product_id,sum(quantity) as total_sales,cast(sum(quantity)/count(sale_month) as signed) as avg_monthly_sales
from sales_underline
where sale_month between '2024-01' and '2024-06'
group by product_id
),
t2 as (
select product_id, monthly_sales as max_monthly_sales from (
select product_id,sum(quantity) as monthly_sales, row_number() over(partition by product_id order by sum(quantity) desc) rk
from sales_underline
where sale_month between '2024-01' and '2024-06'
group by product_id,sale_month
) a where rk = 1
),
t3 as (
select product_id, monthly_sales as min_monthly_sales from (
select product_id,sum(quantity) as monthly_sales, row_number() over(partition by product_id order by sum(quantity) asc) rk
from sales_underline
where sale_month between '2024-01' and '2024-06'
group by product_id,sale_month
) b where rk = 1
)
select 
p.product_id,
p.product_name,
total_sales,
max_monthly_sales,
min_monthly_sales,
avg_monthly_sales
from products_underline p
join t1 using(product_id)  
join t2 using(product_id)
join t3 using(product_id)
order by product_id asc
;