🚀BEATS 82% 🚀 | | 🔥EASY MySQL Solution 🔥 | | ✅ ROUND, SUM, BETWEEN ✅
Intuition\n Describe your first thoughts on how to solve this problem. \nWe cannot use the pre-defined AVG() function as here there are 2 tables and the formula
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\nWe cannot use the pre-defined AVG() function as here there are 2 tables and the formula for calaculating average selling price is different.\n\n# Approach\n<!-- Describe your approach to solving the problem. -->\nWe will be using the same formula which is given in the porblem description. The formula given for calculating the average selling price is :\nAverage selling price of product x = SUM[units of product x * price of product x(for which the purchase date lies between the start_date and end_Date)] / Total number of units sold of product x sold\n\nFor the numerator and denominator in the above formula we can use the predefined SUM() function in SQL.\n\nAverage selling price of product = SUM(units*price)/SUM(units)\n\nSince, we also want that the purchase date must be in between the start date and end date so we will specify that in code because we want that price for that number of units is corresponding to that purchase date. \n\n# Code\n```\n# Write your MySQL query statement below\nSELECT p.product_id, IFNULL(ROUND(SUM(units*price)/SUM(units),2),0) AS average_price\nFROM Prices p LEFT JOIN UnitsSold u\nON p.product_id = u.product_id AND\nu.purchase_date BETWEEN start_date AND end_date\ngroup by product_id\n```\n\n##### Please upvote if you found it helpful.\n![Upvote picture.jpg](https://assets.leetcode.com/users/images/aceaeaf3-2151-4b68-b4dd-10f84e9b7f15_1691329605.3144674.jpeg)\n\n
['MySQL']