41. An order analysis joins every sale to today's product price. How would you calculate revenue using the price valid when each sale occurred?
HardSQL
Technical
Show model answer
Use the transaction's recorded price if available and appropriate; otherwise join to a price history using product identity and a validity interval containing the sale timestamp. Specify inclusive and exclusive boundaries so adjacent intervals cannot both match.
Check for overlapping intervals, gaps and time-zone mismatches before aggregating. Preserve the applicable price version and reconcile a small set of known sales, rather than letting multiple historical prices silently multiply revenue.
What interviewers listen for
- Prefer the recorded transaction price
- Enforce unambiguous validity intervals
- Check gaps, overlaps and reconciliation