SQL Query TO FIND THE AVERAGE PRICE OF EACH PRODUCT -Intreview Question

Write a SQL Query TO FIND THE AVERAGE PRICE OF EACH PRODUCT 

-------------------START-----------------------------------------------------------------------

 

CREATE TABLE Price

(

product_id INT NOT NULL,

start_date DATE,

end_date DATE,

price INT

)

insert Price values (1,GETDATE()-90,GETDATE()-70,100)

insert Price values (1,GETDATE()-300,GETDATE()-100,120)

insert Price values (2,GETDATE()-400,GETDATE()-300,220)

insert Price values (2,GETDATE()-600,GETDATE()-500,320)

SELECT * FROM Price

SELECT * FROM Unitsold

CREATE TABLE Unitsold

(

product_id INT NOT NULL,

purchase_date DATE,

units INT)

insert  Unitsold values(1,GETDATE()-80,22)

insert  Unitsold values(1,GETDATE()-200,30)

insert  Unitsold values(2,GETDATE()-340,40)

insert  Unitsold values(2,GETDATE()-540,50)

SELECT * FROM Price

SELECT * FROM Unitsold

---------------------------------------------------SOLUTION---------------------------------

with CTE_1 as(
select product_id,units,units*price as product_price
 from(
select us.product_id,purchase_date,units,price from
Price p join Unitsold us on p.product_id=us.product_id
and purchase_date between start_date and end_date)A)
select product_id,sum(product_price)/sum(units) as 'Average_price'
from CTE_1 group by product_id