EASY SOLUTION
We need product_id and average_price.\n\t For product_id  we will select product_id from Price tabel as p\n\t For average_price: SUM(priceunits) / SUM(units)\n2
1. We need product_id and average_price.\n\t* For product_id  we will select product_id from Price tabel as p\n\t* For average_price: SUM(price*units) / SUM(units)\n2. we will perform left join bcz we need all data from prices table.\n3.  we need to compair product id\'s and we aslo need to ensure that the purchase date is between start date and end date.\n4.   so we will left join on price.product_id = unitSold.product_id AND unitSold.purchase_date between price.start_date and start.end_date.\n5.    Atlast we need to group multiple entries. so we will group by price.product_id\n\n**THIS SOLUTION WILL ACCEPTED BUT WHEN WE SUBMIT IT WILL THROUGH AN ERROR FOR PRODUCT WHERE THERE IS NO UNIT SOLD.**\n```\nSELECT p.product_id, ROUND(SUM(p.price*us.units)/SUM(us.units),2) AS average_price\nFROM Prices AS p\nLEFT JOIN UnitsSold AS us\nON p.product_id = us.product_id AND us.purchase_date BETWEEN p.start_date and p.end_date\nGROUP BY p.product_id;\n```\n**TO DEAL WITH THAT ERROR WE WILL USE IFNULL FUNCTION.\nCORRECT SOLUTION**\n\nIFNULL SYNTAX: IFNULL(expression, alt_value)\n\nHere, IFNULL check if result is NULL then it will return 0.\n```\nSELECT p.product_id, IFNULL(ROUND(SUM(p.priceus.units)/SUM(us.units),2),0) AS averageprice\nFROM Prices AS p\nLEFT JOIN UnitsSold AS us\nON p.product_id = us.product_id AND us.purchase_date BETWEEN p.start_date and p.end_date\nGROUP BY p.product_id;\n```
['MySQL']