1251. Average Selling Price

https://leetcode.com/problems/average-selling-price/description/

 

15번째 케이스에서 Prices 테이블의 product_id가 3이상으로 추가되면서,

 

UnitSold 테이블에 없는 데이터 값도 결과값에 으로 나타나야 했기에 INNER 를 LEFT로 수정하고,

조건 결과값으로, Null값도 함께 나타날 수 있도록 WHERE문 끝에 "or UnitsSold.product_id IS NULL" 을 이어붙인 뒤 COALESCE를 ROUND 앞에 씌워 NULL값 처리를 해야했다.

 

 

 

잠깐 생각하게 만들었던 문제

다음부턴 예시에 보이지 않는 테스트 케이스를 예상해서 작성해봐야겠다.

 

*** JOIN에 AND 조건을 덧붙이는 방식보다, WHERE로 조건을 추가 작성하는 것이 확실히 쿼리속도가 더 빨랐다.

 

 

 

Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places.

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

 

 

 

 

SELECT Prices.product_id
    --   ,start_date
    --   ,end_date
    --   ,price
    --   ,SUM(units)
    --   ,SUM(price*units) AS mutiply_price_units
      ,COALESCE(ROUND(SUM(price*units)/SUM(units),2),0) AS average_price

FROM Prices
     LEFT JOIN UnitsSold ON Prices.product_id = UnitsSold.product_id
WHERE UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date or UnitsSold.product_id IS NULL
GROUP BY product_id