Efficient Calculation of Average Product Prices in SQL
Code\n\n# Write your MySQL query statement below\nSELECT \n    p.product_id, \n    CASE \n        WHEN SUM(u.units) IS NULL THEN 0\n        WHEN SUM(u.units) = 
# Code\n```\n# Write your MySQL query statement below\nSELECT \n    p.product_id, \n    CASE \n        WHEN SUM(u.units) IS NULL THEN 0\n        WHEN SUM(u.units) = 0 THEN 0\n        ELSE ROUND(SUM(p.price * u.units) / SUM(u.units), 2)\n    END AS average_price\nFROM prices p \nLEFT JOIN unitsSold u \nON p.product_id = u.product_id \n    AND u.purchase_date BETWEEN p.start_date AND p.end_date\nGROUP BY p.product_id;\n```\n# Approach\n<!-- Describe your approach to solving the problem. -->\nThe strategy is to use a LEFT JOIN to combine prices and unitsSold, then group by product_id. The CASE statement handles different scenarios: no units sold or all units are zero. Then, calculate the average price using a conditional expression.\n# Complexity\n- Time complexity: $$O(n)$$\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\n\n- Space complexity: $$O(1)$$\n<!-- Add your space complexity here, e.g. $$O(n)$$ -->\n![LeetCode-Upvote.png](https://assets.leetcode.com/users/images/e5d14c43-82e9-4755-88bb-01e9d6bb74d5_1702963136.7280915.png)\n\n
['MySQL']