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

SELECT
  product_id,
  p.product_name,
  SUM(
    CASE
      WHEN o.order_date BETWEEN '2024-04-01' AND '2024-06-30'
      THEN o.total_amount
      ELSE 0
    END
  ) AS q2_2024_sales_total,
  ROW_NUMBER() OVER (
    PARTITION BY p.category
    ORDER BY
      SUM(
        CASE
          WHEN o.order_date BETWEEN '2024-04-01' AND '2024-06-30'
          THEN o.total_amount
          ELSE 0
        END
      ) DESC
  ) AS category_rank,
  s.supplier_name
FROM product_info p
LEFT JOIN order_info o
  USING (product_id)
LEFT JOIN supplier_info s
  USING (product_id)
GROUP BY
  product_id,
  p.product_name,
  s.supplier_name,
  p.category
order by product_id asc;