SQL | LeetCode 1251. 날짜 범위 매핑으로 평균 판매가 구하기

Table: Prices

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+
(product_id, start_date, end_date) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates the price of the product_id in the period from start_date to end_date.
For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.

 

Table: UnitsSold

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+
This table may contain duplicate rows.
Each row of this table indicates the date, units, and product_id of each product sold. 

 

Write a solution to find the average selling price for each product. average_priceshould 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.

The result format is in the following example.

 

Example 1:

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: 
Average selling price = Total Price of Product / Number of products sold.
Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96
Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96

Prices 테이블에는 제품의 기간별 가격 정보, UnitsSold 테이블에는 판매된 날짜와 수량 정보가 주어져 있습니다. 우리는 각 제품의 평균 판매 가격을 구해야 합니다.

SELECT 
  *
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
  
| product_id | start_date | end_date   | price | product_id | purchase_date | units |
| ---------- | ---------- | ---------- | ----- | ---------- | ------------- | ----- |
| 1          | 2019-02-17 | 2019-02-28 | 5     | 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01 | 2019-03-22 | 20    | 1          | 2019-03-01    | 15    |
| 2          | 2019-02-01 | 2019-02-20 | 15    | 2          | 2019-02-10    | 200   |
| 2          | 2019-02-21 | 2019-03-31 | 30    | 2          | 2019-03-22    | 30    |

판매 기록이 없는 제품도 결과에 포함되어야 하므로 Prices를 기준으로 UnitsSold를 LEFT JOIN합니다.

SELECT 
  p.product_id,
  SUM(p.price * u.units) AS total_revenue,
  SUM(u.units) AS total_units
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 p.product_id

| product_id | total_revenue | total_units |
| ---------- | ------------- | ----------- |
| 1          | 800           | 115         |
| 2          | 3900          | 230         |

이제 제품별로 총 수익 = price × units 과 총 수량 = units 을 구해서 평균 가격을 계산할 수 있습니다.

SELECT 
  p.product_id,
  ROUND(COALESCE(SUM(u.units * p.price) / NULLIF(SUM(u.units), 0), 0), 2) AS 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 p.product_id

| product_id | average_price |
| ---------- | ------------- |
| 1          | 6.96          |
| 2          | 16.96         |

평균 가격은 total_revenue / total_units인데, 판매 수량이 0이면 0으로 처리해야 하니까 NULLIF와 COALESCE를 사용합니다.