New knowledge about between.
If you don't know exactly what the code does, you can see this:
select p.product_id, p.start_date, p.end_date, p.price, u.purchase_date, u.units
from Prices p inner join UnitsSold u
on p.product_id=u.product_id and u.purchase_date between p.start_date and p.end_dateThe result of this code is:
| product_id | start_date | end_date | price | purchase_date | units |
| ---------- | ---------- | ---------- | ----- | ------------- | ----- |
| 1 | 2019-02-17 | 2019-02-28 | 5 | 2019-02-25 | 100 |
| 1 | 2019-03-01 | 2019-03-22 | 20 | 2019-03-01 | 15 |
| 2 | 2019-02-01 | 2019-02-20 | 15 | 2019-02-10 | 200 |
| 2 | 2019-02-21 | 2019-03-31 | 30 | 2019-03-22 | 30 |
# Write your MySQL query statement below
select p.product_id, round(sum(p.price*u.units)/sum(units), 2) as average_price
from Prices p join UnitsSold u
on p.product_id=u.product_id and u.purchase_date between p.start_date and p.end_date
group by product_id- 2024.06.02