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

with
    t as (
        select
            product_id,
            product_name,
            sum(total_amount) as q2_2024_sales_total,
            category
        from
            order_info
            join product_info using(product_id)
        where
            order_date between '2024-04-01' and '2024-06-30'
        group by
            product_id,
            product_name
    )
select
    product_id,
    product_name,
    case
        when q2_2024_sales_total is null then 0
        else q2_2024_sales_total
    end as q2_2024_sales_total,
    
    rank() over (
        partition by
            category
        order by
            q2_2024_sales_total DESC
    ) as category_rank,
    supplier_name
from
    product_info
    join supplier_info using(product_id)
    left join t using (product_id, product_name, category)
order by product_id