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

WITH a AS(
    SELECT product_info.product_id, product_name, category, total_amount 
    FROM product_info
    LEFT JOIN order_info ON product_info.product_id = order_info.product_id 
    AND order_date BETWEEN '2024-04-01' AND '2024-06-30'
),
b AS(
    SELECT product_name, product_id, category, ROUND(SUM(COALESCE(total_amount, 0)),2) q2_2024_sales_total
    FROM a 
    GROUP BY category, product_name, product_id
),
c AS(
    SELECT category, product_name, product_id, q2_2024_sales_total, 
        DENSE_RANK() OVER(PARTITION BY category ORDER BY q2_2024_sales_total DESC) category_rank 
    FROM b
)
SELECT c.product_id, product_name, q2_2024_sales_total, category_rank,  supplier_name
FROM c
JOIN supplier_info USING(product_id)
ORDER BY c.product_id