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

SELECT 
    product_id,
    product_name,
    q2_2024_sales_total,
    ROW_NUMBER() OVER(PARTITION BY category ORDER BY q2_2024_sales_total DESC) AS category_rank,
    supplier_name 
FROM (
    SELECT
        p.product_id,
        product_name,
        category,
        SUM(CASE WHEN MONTH(order_date) IN (4,5,6) THEN total_amount ELSE 0.00 END) AS q2_2024_sales_total,
        supplier_name 
    FROM
        product_info p
        LEFT JOIN order_info o ON p.product_id = o.product_id 
        LEFT JOIN supplier_info s ON p.product_id = s.product_id 
    GROUP BY
        1,2,3,5 
) t 
ORDER BY product_id;