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

WITH t AS(
    SELECT p.product_id,
           p.product_name,
           COALESCE(SUM(total_amount),0) AS q2_2024_sales_total
    FROM order_info o
    RIGHT JOIN product_info p ON p.product_id=o.product_id AND order_date  BETWEEN '2024-04-01' AND  '2024-06-01'
    GROUP BY p.product_id,p.product_name
)
SELECT t.product_id,
       t.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 t 
LEFT JOIN product_info  p ON p.product_id=t.product_id
LEFT JOIN supplier_info s ON t.product_id=s.product_id
ORDER BY t.product_id