orders(order_id, product_id, ordered_at, quantity) and prices(product_id, valid_from, price)

A product's price is whatever the prices row with the latest valid_from at or before ordered_at says. Attach that price to each order and compute order_value = price * quantity.

Return order_id, product_id, price and order_value, ordered by order_id. An order placed before the product had any price at all is left out.

Joining on valid_from <= ordered_at alone multiplies the order by every historic price, so the latest one still has to be picked out.

Tables

orders

order_id  product_id  ordered_at           quantity
--------  ----------  -------------------  --------
1         1           2026-02-15 10:00:00  2
2         1           2026-03-01 00:00:00  3
3         1           2026-07-04 10:00:00  1
4         2           2026-04-30 23:59:59  5
5         2           2026-05-02 10:00:00  4

prices

product_id  valid_from           price
----------  -------------------  ------
1           2026-01-01 00:00:00  10.00
1           2026-03-01 00:00:00  12.00
1           2026-06-01 00:00:00  9.50
2           2026-05-01 00:00:00  100.00

Expected result

order_id  product_id  price   order_value
--------  ----------  ------  -----------
1         1           10.00   20.00
2         1           12.00   36.00
3         1           9.50    9.50
5         2           100.00  400.00