✅[MySQL] Simple and Clean, beats 93.34%✅
Please upvote if you find this helpful. \u270C\n\n\n# Intuition\nThe problem asks us to calculate the average price of each product, weighted by the number of u
### Please upvote if you find this helpful. \u270C\n<img src="https://assets.leetcode.com/users/images/b8e25620-d320-420a-ae09-94c7453bd033_1678818986.7001078.jpeg" alt="Cute Robot - Stable diffusion" width="200"/>\n\n# Intuition\nThe problem asks us to calculate the average price of each product, weighted by the number of units sold within a given date range. Our first thought might be to join the `Prices` and `UnitsSold` tables, filter the results by the given date range, group the results by `product_id`, and then calculate the weighted average of the `price` column for each group.\n\n# Approach\n1. Join the `Prices` and `UnitsSold` tables using the `product_id` column.\n2. Filter the results by the given date range using the `purchase_date`, `start_date`, and `end_date` columns.\n3. Group the results by `product_id`.\n4. Calculate the weighted average of the `price` column for each group using the `SUM` and `AVG` functions.\n5. Round the result to 2 digits using the `ROUND` function.\n\n# Complexity\n- Time complexity: Depends on the size of the `Prices` and `UnitsSold` tables, as well as the efficiency of the join and filter operations.\n- Space complexity: Depends on the size of these tables and the number of groups created by the `GROUP BY` clause.\n\n\n# Code\n```\nselect p.product_id, round(sum(p.price*u.units)/sum(u.units),2) as average_price\nfrom Prices p\njoin UnitsSold u on p.product_id = u.product_id and purchase_date between start_date and end_date\ngroup by p.product_id;\n```
['MySQL']