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

# 查询商品在24年上半年的总销量、销量最高月份的销售量、销量最低月份的销售量、平均月销售量

with
t1 as(
    select
        product_id,
        product_name,
        sale_month,
        sum(quantity) as monthly_sales,
        count(product_id)over(partition by product_id) as month_count
    from
        sales_underline left join products_underline using(product_id)
    where
        sale_month between '2024-01' and '2024-06'
    group by
        product_id,
        product_name,
        sale_month
)
,
t2 as(
    select distinct
        product_id,
        product_name,
        sum(monthly_sales)over(partition by product_name) as total_sales,
        max(monthly_sales)over(partition by product_name) as max_monthly_sales,
        min(monthly_sales)over(partition by product_name) as min_monthly_sales,
        month_count
    from
        t1
)

select
        product_id,
        product_name,
        total_sales,
        max_monthly_sales,
        min_monthly_sales,
        round(total_sales/month_count,0) as avg_monthly_sales
from
    t2
order by
    product_id