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

with t1 as(
select s.product_id
, product_name
, sum(quantity) total_sales
from sales_underline s
left join products_underline p
on s.product_id = p.product_id 
where sale_month between '2024-01' and '2024-06'
group by 1,2)

, t2 as(
select product_id,product_name,max_monthly_sales,min_monthly_sales,avg_monthly_sales
from (
select s.product_id
, product_name
, max(quantity) over(partition by s.product_id ) max_monthly_sales
, min(quantity) over(partition by s.product_id ) min_monthly_sales
, round(avg(quantity) over(partition by s.product_id ),0) avg_monthly_sales
from sales_underline s
left join products_underline p
on s.product_id = p.product_id
where sale_month between '2024-01' and '2024-06'
) a
group by 1,2,3,4,5
)

select t1.product_id
, t1.product_name
, total_sales
, max_monthly_sales
, min_monthly_sales
, avg_monthly_sales
from t1
left join t2
on t1.product_id = t2. product_id