How to correctly calculate the revenue of the product when the price changes?

I have two datasets:

prices: records of product’s history price changes in Sep 2018
sales: records of product’s sales in Sep 2018 Use any data wrangling tools to calculcate the total revenue of each product and revenue at each price.

Thanks a lot.

Prices (sample):

product_id	old_price	new_price	updated_at
64	270000	239000	9/10/2018 4:37:00 PM
3954203	60000	64000	9/11/2018 10:59:00 PM
3954203	64000	60500	9/17/2018 11:54:00 AM
3998909	19000	17000	9/10/2018 4:35:00 PM
3998909	17000	15500	9/16/2018 5:09:00 AM
4085861	67000	62500	9/11/2018 8:51:00 AM
4085861	62500	58000	9/17/2018 3:35:00 AM

Sale (sample):

product_id	quantity_ordered	ordered_at
64	1	9/09/2018 4:37:00 PM
64	6	9/11/2018 10:59:00 PM
3954203	6	9/10/2018 11:54:00 AM
3954203	1	9/12/2018 4:35:00 PM
3954203	1	9/18/2018 5:09:00 AM
4085861	2	9/10/2018 8:51:00 AM
4085861	1	9/19/2018 3:35:00 AM

Calculcate the total revenue of each product and revenue at each price

Solution

you can use two subqueries to determine the price at a specific date

the following takes the last new_price if it finds a row before the sales day else takes the old_price from next available day.

The second subselect would be not necessary, if you had for every produce one initial row, before all sales days where you have the same old_price and new_price.

the product table will profit from a combined INDEX on prodict_id and updated_at

SELECT
  s.`product_id`
  , s.`quantity_ordered`
  ,IFNULL(
  (SELECT `new_price` FROM product p WHERE p.`product_id` = s.`product_id`
  AND  s.`ordered_at`>= p.`updated_at` ORDER BY `updated_at` DESC LIMIT 1)
  ,(SELECT `old_price` FROM product p WHERE p.`product_id` = s.`product_id`
  AND  s.`ordered_at`< p.`updated_at` ORDER BY `updated_at` ASC LIMIT 1))
  p_price
  , s.`ordered_at`
FROM sale s

product_id	quantity_ordered	p_price	ordered_at
64	1	270000	2018-09-09 18:37:00
64	6	239000	2018-09-12 00:59:00
3954203	6	60000	2018-09-10 13:54:00
3954203	1	64000	2018-09-12 18:35:00
3954203	1	60500	2018-09-18 07:09:00
4085861	2	67000	2018-09-10 10:51:00
4085861	1	58000	2018-09-19 05:35:00

fiddle