Easy and Simple Solution
Please Like ❤️IntuitionWe need to calculate the average selling price for each product based on the units sold and price within a valid date range.Approach
Join
```mysql
# Write your MySQL query statement below
select A.product_id, ifNull(round(Sum(price*units)/sum(units) ,2),0) as average_price
from prices as A 
left join UnitsSold as B
on A.product_id=B.product_id
And purchase_date Between start_date and end_date
group by A.product_id;
```

# Please Like ❤️  
![Please Like](https://media.tenor.com/heEyHbV8iaUAAAAM/puss-in-boots-shrek.gif)

# Intuition  
We need to calculate the **average selling price** for each product based on the **units sold and price within a valid date range**.

# Approach  
1. **Join Tables:**  
   - Use `LEFT JOIN` to link `Prices` and `UnitsSold` tables based on `product_id`.  
   - Ensure `purchase_date` falls within the given `start_date` and `end_date` range.  
2. **Compute Weighted Average Price:**  
   - Multiply `price * units` and sum over all entries.  
   - Divide by the total number of units sold (`SUM(units)`).  
   - Use `ROUND(...,2)` to format the result to **two decimal places**.  
   - Use `IFNULL(..., 0)` to handle cases where no units were sold.  
3. **Group By Product:**  
   - The result should be grouped by `product_id`.

# Complexity  
- **Time Complexity:**  
  $$O(n)$$ where `n` is the number of rows in the joined dataset.  
- **Space Complexity:**  
  $$O(1)$$ since we only use aggregate functions and do not store extra data.
['MySQL']