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

/*处理第二季度*/
with order_underline as (
    select *
    from order_info
    where substring(order_date,1,4) = "2024"
    and substring(order_date,6,2) between "04" and "06"
),

/*每个产品2024第二季度总销售额*/
total_sales as (
    select product_id
    /*后面left join 可能会产生空值，题目有要求的话一定要处理，此时目前这一步可以不处理空值，但是遵循尽早处理空值的原则，这里还是处理一下，养成习惯*/
    ,sum(coalesce(total_amount,0.00)) as q2_2024_sales_total
    from order_underline
    group by product_id
),

/*每个产品在同类产品中的销售总额排名*/
rk_total_sales as (
    select p.product_id
    ,product_name
    ,category
    ,coalesce(q2_2024_sales_total,0.00) as q2_2024_sales_total
    ,row_number() over(partition by category order by coalesce(q2_2024_sales_total,0) desc) as category_rank
    from product_info p
    left join total_sales t on p.product_id = t.product_id
)

/*总查询*/
select t1.product_id
,product_name
,q2_2024_sales_total
,category_rank
,supplier_name
from rk_total_sales t1
left join supplier_info s on t1.product_id = s.product_id
order by product_id