Latest price as of each order

Medium20% solve itAsked at Amazon, Flipkart

Prices change over time and orders must be valued at the price in effect when they were placed. Return each order with the price that was current at ordered_at.

Input schema

orders price_history order_id bigint product_id bigint product_id bigint price numeric ordered_at timestamp valid_from timestamp

Constraints

Use the row with the greatest valid_from that is not after ordered_at
Orders placed before any price row return null
Ties on valid_from take the higher price
Order by order_id

Example 1each order takes the price in effect

Input · orders

order_id	ordered_at	product_id
1	2024-02-10 12:00:00	55
2	2024-03-05 08:30:00	55

Input · price_history

price	product_id	valid_from
19.99	55	2024-01-01 00:00:00
24.5	55	2024-03-01 00:00:00

Output

order_id	product_id	ordered_at	price
1	55	2024-02-10 12:00:00	19.99
2	55	2024-03-05 08:30:00	24.5

Example 2prices are per product