Step-by-step detailed solution || Easy to understand || MySQL
Intuition\n Describe your first thoughts on how to solve this problem. \nA beginner friendly answer is written by following basic concepts step by step.\n# Appr
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\nA beginner friendly answer is written by following basic concepts step by step.\n# Approach\n<!-- Describe your approach to solving the problem. -->\nLooking at the result table we write the basic select statement \nSELECT product_id, ____ as average_price FROM ___\n\nAS average_price should be rounded to 2 decimal places\nSELECT p.product_id, round(_____,2) as average_price\n\nNow as evident from Explanation given, the two - price and units, required to calculate average_price are from different tables hence we need to join these tables (as product_id is common in these two tables we join on product_id)\n-> update for the new test case \nI have used LEFT JOIN in place of INNER JOIN, to include all the product_ids in the Prices table even if it in not present in the UnitsSold table.\nSELECT p.product_id, round(_____,2) as average_price\nFROM Prices p LEFT JOIN UnitsSold u \nON p.product_id = u.product_id\n\n-> update for the new test case \nNow as there can be null value for the average_price (as we are left joining tables) to handle these null values (and replace null with a 0 in result table) we can use IFNULL() function as follows\nSELECT p.product_id, IFNULL(round(_____,2),0) as average_price\n\nNow to calculate average_price, we need to add price x units of all the products (of that particulat product_id) and then divide it by the sum of all its units \nThis is formulated as:SUM(p.price*u.units)/SUM(u.units)\nusing SUM() function\n\nFor the calculation of average_price we first need to determine the price per unit based on the purchase date\nHence, modifying JOIN staement by adding the following condition\nu.purchase_date BETWEEN p.Start_date and p.end_date\n\nNow finally to have one product_id only once in the result table and calculate average_price for each product we: Group by product_id\n\nThus the final code becomes\nSELECT p.product_id, IFNULL(round(SUM(p.price*u.units)/sum(u.units),2),0) as average_price\nFROM Prices p LEFT JOIN UnitsSold u\nON p.product_id = u.product_id AND u.purchase_date BETWEEN p.Start_date and p.end_date\nGROUP BY p.product_id\n\nIn the above code Aliases () are used as product_id belongs to both the tables and without them it is difficult to judge which table\'s product_id is being referred to. (i.e product_id will be ambiguous)\n# Complexity\n- Runtime : 1240 ms\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\n\n- Beats: 98.06%\n<!-- Add your space complexity here, e.g. $$O(n)$$ -->\n\n# Code\n```\n# Write your MySQL query statement below\nSELECT p.product_id, IFNULL(round(SUM(p.price*u.units)/sum(u.units),2),0) as average_price\nFROM Prices p \nLEFT JOIN UnitsSold u\nON p.product_id = u.product_id AND \nu.purchase_date BETWEEN p.Start_date and p.end_date\nGROUP BY p.product_id\n```\nIf you like my explanation kindly upvote for me :)
['MySQL', 'Oracle', 'MS SQL Server']