ORIGINAL COMPANY-CONTEXT PRACTICE
Join an order to its historical customer segment
Original practice scenario inspired by SAP SE's public business context; this is not a reported interview question or a description of its internal systems. A fictional enterprise report stores orders and customer-segment history with valid_from and valid_to. Explain a temporal join that assigns the segment valid when each order was placed, including missing or overlapping history.
Written for independent practice using company context; this is not a verified question from a company interview.
A REASONED APPROACH
Model answer.
Use an explicit validity interval at the order timestamp, rather than joining every order to the customer's current segment. Treat overlapping history as a data-quality error, not an arbitrary winning row.
Work through it
- Adopt half-open intervals: valid_from <= order_time and order_time < valid_to, with a documented representation for an open-ended interval.
- Left join history using customer identity and interval predicates. Preserve orders without a matching segment and label their segment unknown.
- Check that each order matches at most one history row. Test exact boundary timestamps, late backfills, gaps, and overlapping versions before aggregating revenue by segment.
Tradeoffs to discuss
- Correcting historical validity can change earlier reports. Versioned snapshots or report run metadata help explain those revisions.
Common pitfalls
- A WHERE condition on history can drop unmatched orders.
- Ranking overlapping matches hides contradictory history instead of resolving its cause.