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

select
    t.product_id,
    product_name,
    total_sales,
    max_monthly_sales,
    min_monthly_sales,
    avg_monthly_sales
from
    (
        select
            product_id,
            sum(quantity) as total_sales,
            max(quantity) as max_monthly_sales,
            min(quantity) as min_monthly_sales,
            round(sum(quantity) / count(*), 0) as avg_monthly_sales
        from
            sales_underline su
        where
            left(sale_month, 4) = '2024'
            and substring_index(sale_month, '-', -1) >= '01'
            and substring_index(sale_month, '-', -1) <= '06'
        group by
            product_id
    ) as t
    inner join products_underline pu on t.product_id = pu.product_id
order by
    pu.product_id asc;