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

with a as (
    select pi.product_id, product_name, sum(total_amount) as q2_2024_sales_total, category
    from product_info pi left join order_info oi on pi.product_id = oi.product_id
    where order_date between '2024-04-01' and '2024-06-30'
    group by pi.product_id
    union
    select product_id, product_name, 0 as q2_2024_sales_total, category
    from product_info
    where product_id not in(
        select product_id 
        from order_info 
        where order_date between '2024-04-01' and '2024-06-30'
    )
),
b as (
    select a.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 a left join supplier_info si on a.product_id = si.product_id
    order by a.product_id
)
select * from b