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

#先计算季度宗旨
with tb as
(
    select
        p.product_id,
        p.product_name,
        sum(case when date(o.order_date) between '2024-01-01' and '2024-06-30' then o.total_amount else 0 end) as q2_2024_sales_total
    from product_info p 
    join order_info o on p.product_id = o.product_id
    group by p.product_id, p.product_name
),
#其次计算按类别分类排名
tb2 as
(
    select
    tb.product_id,
    tb.product_name,
    tb.q2_2024_sales_total,
    rank() over(partition by p.category order by tb.q2_2024_sales_total desc) as rk
    from product_info p
    join tb on tb.product_id = p.product_id
),
#然后连接供应商表-没有刚开始全部连接是害怕product_id“一对多”情况下出现笛卡尔积
tb3 as
(
    select
    tb2.product_id,
    tb2.product_name,
    tb2.q2_2024_sales_total,
    tb2.rk,
    s.supplier_name
    from tb2
    join supplier_info s on tb2.product_id = s.product_id
)
#最后按要求输出
select
product_id,
product_name,
q2_2024_sales_total,
rk as category_rank,
supplier_name
from tb3
order by product_id asc;

代码就这样长长的，思路就这样笨笨的，反正全部通过就行🧓