📊 1251. Average Selling Price | 100% Beats 🚀 | Best SQL Solution ⚡  
💡 IntuitionThe problem requires calculating the average selling price (ASP) for each product. The ASP is determined by the total revenue (units * price) divided


# 💡 Intuition  
The problem requires calculating the **average selling price (ASP)** for each product. The ASP is determined by the total revenue (`units * price`) divided by the total units sold.  

### **Key Observations:**  
- The `Prices` table contains the **price of each product within a valid date range** (`start_date` to `end_date`).  
- The `UnitsSold` table records **how many units were sold on a specific date**.  
- We need to **match the product_id** and ensure that the purchase date falls within the price's validity range.  
- If no sales exist for a product, the average price should be **0**.  
- We use `ROUND()` to format the result to **two decimal places**.  

---

# 🛠️ Approach  
1. **Use `LEFT JOIN`**:  
   - Ensures that products with **no sales** still appear in the result with an average price of `0`.  
   - Join `Prices` with `UnitsSold` on `product_id` and filter by `purchase_date BETWEEN start_date AND end_date`.  
2. **Calculate ASP**:  
   - `SUM(units * price) / SUM(units)` gives the weighted average price.  
   - Handle `NULL` values using `IFNULL()` to return `0` when no sales exist.  
3. **Use `GROUP BY`**:  
   - Groups the result by `product_id` to compute the average price for each product.  

---

# ⏳ Complexity Analysis  
- **Time Complexity**: $$O(n)$$ → Efficient filtering and aggregation using indexed joins.  
- **Space Complexity**: $$O(1)$$ → No extra space used apart from output storage.  

---

# 💻 Code  
```mysql
SELECT p.product_id, 
       IFNULL(ROUND(SUM(u.units * p.price) / SUM(u.units), 2), 0) AS average_price
FROM Prices p 
LEFT JOIN UnitsSold u 
ON p.product_id = u.product_id 
AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;
```

✅ **100% Beats | Best SQL for Average Selling Price 💰 | Simple & Efficient 🚀**

---![a9a682ef-8a56-461e-9e1b-7654b114838f_1721590703.0971212.jpeg](https://assets.leetcode.com/users/images/db969854-29e7-43ff-b3dc-6a9740d66a16_1739461297.63134.jpeg)

['MySQL']