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

-- 逻辑拆解：时间是2024年第二季度，要求商品的销售总额、在各类别中的排名以及供应商
-- 先求出商品的销售综合，再排序，接着去和供应商表做连接
WITH total_sales AS
(
    SELECT
        t1.product_id,
        t1.product_name,
        t1.category,
        COALESCE(SUM(t2.total_amount),0) q2_2024_sales_total,
        DENSE_RANK() OVER(partition by t1.category order by COALESCE(SUM(t2.total_amount),0) DESC) category_rank
    FROM product_info t1
    LEFT JOIN order_info t2
    ON t1.product_id = t2.product_id
    AND t2.order_date BETWEEN '2024-03-01' AND '2024-06-30'
    GROUP BY t1.category,t1.product_id
)
SELECT 
 t1.product_id,
 t1.product_name,
 t1.q2_2024_sales_total,
 t1.category_rank,
 t2.supplier_name
FROM total_sales t1
LEFT JOIN supplier_info t2
ON t1.product_id = t2.product_id
ORDER BY t1.product_id