题解 | 分析每个商品在不同时间段的销售情况

with
    t1 as (
        select distinct
            product_id,
            product_name,
            sum(total_amount) over (
                partition by
                    product_info.product_id
            ) as q2_2024_sales_total,
            category
        from
            product_info
            left join order_info using (product_id)
        where
            order_info.order_date between '2024-04-01' and '2024-06-30'
    )
select
    product_id,
    product_name,
    q2_2024_sales_total,
    rank() over (
        partition by
            category
        order by
            q2_2024_sales_total desc
    ) as category_rank,
    supplier_name
from
    (
        select distinct
            supplier_info.product_id,
            product_info.product_name,
            ifnull(q2_2024_sales_total, 0) as q2_2024_sales_total,
            product_info.category,
            supplier_info.supplier_name
        from
            supplier_info
            left join product_info using (product_id)
            left join t1 using (product_id)
    ) as t2
order by product_id;

写了挺久的，思路不难，难在磨