Skip to interview prep
Apexed

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
An order analysis joins every sale to today's product price. How would you calculate revenue using the price valid when each sale occurred — Data & analytics in