Easy implementation with JOIN | BETWEEN | GROUP BY
This problem requires a good understanding of GROUP BY and how you can aggregate the average_price. At first, you might get an idea to use AVG() but on further 
This problem requires a good understanding of GROUP BY and how you can aggregate the average_price. At first, you might get an idea to use `AVG()` but on further implementation, you will notice that you have to divide by the **total units** and not just the **count of rows**.\n\n# Code\n``` MS_SQL []\nSELECT p.product_id, ISNULL(ROUND(CAST(SUM(price * units) AS FLOAT)/SUM(units), 2), 0) as average_price\nFROM Prices p\nLEFT JOIN UnitsSold u\nON p.product_id = u.product_id\nAND purchase_date BETWEEN start_date AND end_date\nGROUP BY p.product_id\n```\n``` MySQL []\nSELECT p.product_id, IFNULL(ROUND(CAST(SUM(price * units) AS FLOAT)/SUM(units), 2), 0) as average_price\nFROM Prices p\nLEFT JOIN UnitsSold u\nON p.product_id = u.product_id\nAND purchase_date BETWEEN start_date AND end_date\nGROUP BY p.product_id\n```\n``` Oracle []\nSELECT p.product_id, NVL(ROUND(CAST(SUM(price * units) AS FLOAT)/SUM(units), 2), 0) as average_price\nFROM Prices p\nLEFT JOIN UnitsSold u\nON p.product_id = u.product_id\nAND purchase_date BETWEEN start_date AND end_date\nGROUP BY p.product_id\n```\n\n# Intuition\n1. `LEFT JOIN ` the two tables on **product_id** and the **purchase_date**.\n2. Specify the condition to contain the purchase date within the start_date and the end_date using `BETWEEN`. You can also use `<` or `>`.\n3. `GROUP BY` the product_id to **group similar product_id**\n4. `ROUND(CAST(SUM(price * units) AS FLOAT)/SUM(units), 2)` calculates the average by multiplying the price and units to find the total price. \n5. Since we require a decimal value, we need to `CAST` it as **float/decimal/numeric**.\n6. `ROUND` the value to 2 decimal places as required.\n7. Replace any null occurrences using either `IFNULL` in MySQL, `ISNULL` in MS SQL, `NVL` in Oracle.
['MySQL', 'Oracle', 'MS SQL Server']