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

# 查询q2的销售额，在类别中的排名，供应商名称
with
t1 as(
    select
        order_id,
        product_id,
        order_date,
        (
            case
                when order_date between '2024-04-01' and '2024-06-30' then total_amount
                else 0.00
            end
        ) as total_amount
    from
        order_info
)
,
t2 as(
    select
        product_id,
        product_name,
        sum(total_amount) as q2_2024_sales_total,
        rank()over(partition by category order by sum(total_amount) desc) as category_rank,
        supplier_name
    from
        t1
        left join product_info using(product_id)
        left join supplier_info using(product_id)
    group by
        product_id,
        product_name
    order by
        product_id
)

select * from t2