38. Price Valid at Order Time (Range Join)

Difficulty: Hard · Topics: Joins, Null Handling, Filtering & Selection

prices keeps price history: each row is valid from valid_from to valid_to inclusive, and valid_to is null for the current price. Dates are YYYY-MM-DD strings.

For every row in orders, find the price that was valid on order_date and compute revenue = qty × price. Keep orders with no valid price (price and revenue null).

Return order_id, product, order_date, price, revenue.

Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.

Sample data

orders

order_id	product	order_date	qty
1	pen	2024-01-15	10
2	pen	2024-03-01	5
3	ink	2024-02-10	2

prices

product	price	valid_from	valid_to
pen	2	2024-01-01	2024-02-29
pen	3	2024-03-01	null
ink	7	2024-01-01	null

Expected output

order_id	product	order_date	price	revenue
1	pen	2024-01-15	2	20
2	pen	2024-03-01	3	15
3	ink	2024-02-10	7	14

Hints

Hint 1

This is a join on the product and a date range, not just a key.

Hint 2

Treat an open-ended valid_to as far in the future: F.coalesce("valid_to", F.lit("9999-12-31")).

Hint 3

Use a left join so unmatched orders survive.

PySpark functions you'll practise

join
coalesce
select

Browse

Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings

Difficulty: Easy · Medium · Hard · PySpark interview roadmap · All problems