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

SELECT
  t1.product_id,
  t1.product_name,
  t1.q2_2024_sales_total,
  DENSE_RANK() OVER (PARTITION BY t1.category ORDER BY t1.q2_2024_sales_total DESC) AS category_rank,
  s.supplier_name
FROM
  (
    SELECT
      p.product_id,
      p.product_name,
      p.category,
      COALESCE(SUM(o.total_amount), 0.00) AS q2_2024_sales_total
    FROM
      product_info p
      LEFT JOIN order_info o 
        ON p.product_id = o.product_id
        AND o.order_date BETWEEN '2024-04-01' AND '2024-06-30'
    GROUP BY
      p.product_id,
      p.product_name,
      p.category
  ) t1
  LEFT JOIN supplier_info s ON s.product_id = t1.product_id
ORDER BY
  t1.product_id ASC