WrItE tHe QUeRy On Ur OwN bY rEaDiNg ThE ExpLaNaTiOn 💥
🧠 What’s Happening?
We wanna find the average selling price per product.
But 👀Prices change across datesUnits sold on different days
So we gotta match both care
🧠 What’s Happening?
We wanna find the average selling price per product.
But 👀

Prices change across dates

Units sold on different days
So we gotta match both carefully
.
.
🔗 We use LEFT JOIN
— because even if a product wasn’t sold, it should be in the final result
.
.
📅 BETWEEN helps us find the right price for the date the item was sold
.
.
💰 Revenue = price * units
➕ Add it up using SUM(price * units)
.
.
📦 Also track SUM(units) sold
.
.
🧮 Final step = divide the totals to get avg price
But wait...
what if nothing’s sold? 😩
.
.
We gotchu 👉 IFNULL(..., 0)
It turns NULL into 0 — so no NaN drama
.
.
🧊 ROUND everything to 2 decimals using ROUND(..., 2)
.
.
🚨 Can’t use AVG(price) here
Because price changes and we need weighted avg
.
.

# Code
```mysql []
# Write your MySQL query statement below
select p.product_id, ifnull(round(sum(p.price*u.units)/sum(u.units), 2),0) as average_price
from
prices as p
left join 
unitssold as u
on
p.product_id = u.product_id  
and
purchase_date between 
start_date and end_date
group by product_id

```
['MySQL']