[Leetcode] List the Products Ordered in a Period

문제 링크

정답

SELECT t2.product_name, t1.unit
FROM (
    SELECT
        product_id,
        SUM(unit) unit
    FROM Orders
    WHERE order_date BETWEEN '2020-02-01' AND '2020-02-29'
    GROUP BY product_id
) t1
JOIN Products t2
ON t1.product_id = t2.product_id
WHERE t1.unit >= 100;

✅ Point

• 날짜 조건 필터링: 2020-02-01 ~ 2020-02-29
  • BETWEEN 또는 >= AND < 로 필터링 가능
  • 윤달 여부가 헷갈릴 땐 BETWEEN AND 대신 부등호로 대체 가능

다른 풀이

• group by를 sub query에서 진행하지 않고 바로 main query에서 진행

  SELECT P.product_name, SUM(O.unit) unit
  FROM Orders O
  JOIN Products P
  ON O.product_id = P.product_id
  WHERE O.order_date >= '2020-02-01' AND O.order_date < '2020-03-01'
  GROUP BY O.product_id
  HAVING SUM(O.unit) >= 100;