[LeetCode] 1251 - Average Selling Price

문제

문제 : https://leetcode.com/problems/average-selling-price/description/?envType=study-plan-v2&envId=top-sql-50

 

테이블 : Prices

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+

(product_id, start_date, end_date)는 테이블의 PK이다.

테이블의 각 행은 start_date에서 end_date까지 기간 동안의 product_id의 가격을 나타낸다.

동일한 product_id에 대해 교차하는 기간은 존재하지 않는다.

 

테이블 : UnitsSold

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+

테이블에 중복 행이 포함될 수 있다. 각 행은 판매된 제품의 date, units, product_id를 나타낸다.

 

주어진 테이블을 이용하여 각 제품의 평균 판매 가격을 구하여라. average_price는 소수점 두 자리로 반올림해야 한다. 제품 판매 단위가 없는 경우, 평균 판매 가격은 0으로 가정한다.

 

예시 )

Input: 
Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 1          | 2019-02-17 | 2019-02-28 | 5      |
| 1          | 2019-03-01 | 2019-03-22 | 20     |
| 2          | 2019-02-01 | 2019-02-20 | 15     |
| 2          | 2019-02-21 | 2019-03-31 | 30     |
+------------+------------+------------+--------+
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+
Output: 
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
Explanation: 
평균 판매 가격 = 제품의 전체 가격 / 제품 판매 수
product 1 평균 판매 가격 = ((100 * 5) + (15 * 20)) / 115 = 6.96
product 2 평균 판매 가격 = ((200 * 15) + (30 * 30)) / 230 = 16.96

 

 

풀이

두 개 테이블을 product_id를 기준으로 조인한 뒤, 판매일이 start_date와 end_date 사이에 있으면 price를 그대로 유지하고 그렇지 않으면 0으로 설정한다. unit로 동일하게 처리한 뒤 해당 테이블에서 product_id 기준 평균 판매액을 계산한다.

SELECT A.product_id
     , IFNULL(ROUND(SUM(A.price * A.units) / SUM(A.units), 2), 0) AS average_price
  -- 각 product_id의 판매액과 판매 수
  FROM (SELECT Prices.product_id
             , CASE WHEN UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date THEN Prices.price ELSE 0 END AS price
             , CASE WHEN UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date THEN UnitsSold.units ELSE 0 END AS units 
          FROM Prices
          LEFT JOIN UnitsSold
            ON Prices.product_id = UnitsSold.product_id) AS A
GROUP BY A.product_id

 

 

답안

답안을 확인해보니 훨씬 효율적인 방법이 있어 추가로 적어둔다.

여태 JOIN 시 ON 절에 조인의 기준인 컬럼만 지정을 했었는데, 아예 조건을 줄 수도 있다……!

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

즉, 판매일이 start_date와 end_date 사이인 것을 기준으로 조인하면 서브쿼리를 사용하지 않고 원하는 데이터를 뽑을 수 있다.

 

 

참고 문서

https://leetcode.com/problems/average-selling-price/description/?envType=study-plan-v2&envId=top-sql-50