[SQL] JOIN 절에 조건 추가하기 | leetcode - average selling price

코드카타 92번 - average-selling-price

문제

Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places. If a product does not have any sold units, its average selling price is assumed to be 0.

Return the result table in any order.

접근 방법

Price 테이블과 UnitsSold 테이블을 결합하는 게 가장 큰 관건이었다. 지금까지의 JOIN 절에서는 join의 기준이 되는 칼럼명만 명시해주었는데, 이 이외의 조건을 이용하면 두 테이블을 조인할 수 있다.

FROM Prices p LEFT JOIN UnitsSold u ON p.product_id = u.product_id
    AND u.purchase_date BETWEEN p.start_date AND p.end_date

위 FROM 절에서 첫 번째 줄은 가장 기본적인 JOIN 키를 설정해주고 있다. 그리고 뒤이어 오는 AND 절을 통해 UnitsSold 테이블의 구매일자가 Price 테이블의 start_date와 end_date 사이에 들어오도록 하는 조건을 추가해주었다. '테이블1.키 = 테이블2.키' 조건이 아니더라도 JOIN 시 필요한 조건을 걸어줄 수 있는 것을 알 수 있다.

전체 코드

SELECT p.product_id, 
    coalesce(ROUND(SUM(coalesce(u.units, 0) * p.price) /  SUM(u.units), 2), 0) average_price
FROM Prices p LEFT JOIN UnitsSold u ON p.product_id = u.product_id
    AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY 1