Beats 92.22% of runtime.
Intuition\nNeed to apply where method to find where the purchase_date lies in the range of start_date and end_date while joining the tables.\n\n# Approach\n Des
# Intuition\nNeed to apply where method to find where the purchase_date lies in the range of start_date and end_date while joining the tables.\n\n# Approach\n<!-- Describe your approach to solving the problem. -->\n<ol>\n<li>Make a table which contains only required columns(product_id,units,prices) by using <b>Left join approach</b> (since there might be products which will never be sold).</li>\n<li>Now apply weighted mean by using <b>Group by</b> method on product_id.</li>\n<li>Replace the rows which have null values with 0 using <b>COALESCE</b> method.</li>\n</ol>\n\n\n# Code\n```\n# Write your MySQL query statement below\nselect product_id,coalesce(round(sum(price*units)/sum(units),2),0) as average_price from (select p.product_id as product_id,p.price as price,u.units as units from Prices as p left join UnitsSold as u on p.product_id = u.product_id and p.start_date <= u.purchase_date and u.purchase_date <= p.end_date) as t group by product_id\n```
['MySQL']