解题思路:
首先对Prices表和UnitsSold表进行Left join操作,之后按照购买日期位于定价开始和结束日期之间的条件进行过滤
按照product_id进行分组,对组内进行计算
将price和units的乘积进行累加,将units的值进行累加,之后将两个累加的和相除得到最终结果
题解:leetcode题解
代码:
select p.product_id, ifnull(round(sum(price*units)/sum(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 product_id;