Easy solution with explanation
Intuition\nTo calculate the average price for each product:\n\n1. Every product in the Prices table should appear in the output, even if it has no sales.\n2. Us
# Intuition\nTo calculate the average price for each product:\n\n1. Every product in the Prices table should appear in the output, even if it has no sales.\n2. Use the UnitsSold table to calculate total sales and total units, ensuring that missing data results in an average price of 0.\n\n# Approach\n1. Start with the Prices table and use a LEFT JOIN with UnitsSold so that all products in Prices are included.\n2. Match rows based on product_id and ensure purchase_date falls between start_date and end_date.\n3. Compute:\n\n   - Total price: SUM(u.units * p.price). Use COALESCE to handle cases where no matching rows exist in UnitsSold.\n   - Total units sold: SUM(u.units). Again, use COALESCE to default to 1 to avoid division by zero.\n\n4. Compute the average price by dividing the total price by total units, and round it to 2 decimal places.\n5. Use GROUP BY on product_id to aggregate results for each product.\n\n# Complexity\n- **Time complexity**: The query involves a join and grouping operation. Assuming n rows in Prices and m rows in UnitsSold:\n\n    - Join operation: $$O(n\xD7m)$$\n    - Grouping operation:$$ O(n+m)$$\n    - The overall complexity is approximately $$O(n\xD7m)$$ in the worst case.\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\n\n- **Space complexity**:$$O(n+m)$$\nThe query uses memory to store intermediate join results and aggregates, proportional to the size of the tables.\n\n# Code\n```mysql []\nSELECT\n    p.product_id,\n    ROUND(COALESCE(SUM(u.units * p.price), 0) * 1.0 / COALESCE(SUM(u.units), 1), 2) AS average_price\nFROM\n    Prices p\nLEFT JOIN\n    UnitsSold u\nON\n    p.product_id = u.product_id\n    AND u.purchase_date BETWEEN p.start_date AND p.end_date\nGROUP BY\n    p.product_id;\n\n```
['MySQL']