Step by Step Explanation with Optimum Solution
Please Upvote my solution, if you find it helpful ;)\n\n# Intuition\nThe goal is to calculate the average selling price for each product based on the units sold
## ***Please Upvote my solution, if you find it helpful ;)***\n\n# Intuition\nThe goal is to calculate the average selling price for each product based on the units sold and their corresponding prices within a specified time period.\n\n# Approach\n1. Join the "Prices" table with the "UnitsSold" table using the "product_id" column to obtain the relevant data for each product.\n1. Filter the data based on the purchase date being between the start and end dates specified in the "Prices" table.\n1. Calculate the average price by dividing the sum of (price * units) by the sum of units for each product.\n1. Round the average price to two decimal places to match the required output format.\n1. Group the results by the product_id to calculate the average price for each product.\n1. Sort the results in ascending order based on the product_id.\n\n# Complexity\n- Time complexity:\nThe time complexity of this solution depends on the size of the "Prices" table and the "UnitsSold" table. Let\'s assume the sizes of these tables are n and m, respectively. The join operation will take $$O(n + m)$$ time complexity. The subsequent calculations and grouping will also take $$O(n + m)$$ time complexity. Therefore, the overall time complexity is $$O(n + m)$$.\n\n- Space complexity:\nThe space complexity of this solution depends on the number of distinct product_ids in the "Prices" table. Let\'s assume there are k distinct product_ids. The space required to store the output results is O(k) since we have k rows in the result set. The additional space required for processing and joining the tables is $$O(n + m)$$. Therefore, the overall space complexity is $$O(n + m + k)$$.\n\nOverall, your solution efficiently calculates the average selling price for each product within the specified time period.\n\n# Code\n```\n# Write your MySQL query statement below\n\nSELECT \n    p.product_id, \n    round(sum(price*units)/sum(units),2) AS average_price \nFROM Prices p\nJOIN UnitsSold u using (product_id)\nWHERE u.purchase_date between p.start_date AND p.end_date\nGROUP BY p.product_id\n```\n***Please Upvote my solution, if you find it helpful ;)***\n![6a87bc25-d70b-424f-9e60-7da6f345b82a_1673875931.8933976.jpeg](https://assets.leetcode.com/users/images/b2463670-a1d1-42e8-b0fd-165b6acb9d79_1685736992.4540915.jpeg)\n\n
['Database', 'MySQL']