# 문제
https://leetcode.com/problems/average-selling-price/description/
# 문제 설명
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
# 핵심 개념
1. 테이블 2개 이상 조인 문제가 나왔다고 해서 무작정 inner join을 쓰지 말고,
O/O를 구하는지 -> inner join
O/O와 O/X도 구하는지 -> left join
잘 판단한 후 문제 풀기
2. left join으로 O/X에서 null값 나왔을때
ifnull 또는 if로 null값 잘 처리하기
# 정답 코드
select p.product_id,
ifnull(round(sum(u.units * p.price) / sum(u.units),2),0) 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