ORIGINAL COMPANY-CONTEXT PRACTICE
Join transactions to the status valid when they occurred
Original practice scenario: A hypothetical banking report groups transactions by account status, but status history includes closures and reopenings. How should a historical query avoid using today's status?
Written for independent practice using company context; this is not a verified question from a company interview.
A REASONED APPROACH
Model answer.
Join each transaction to an effective-dated status interval. Current account state is a useful operational view but is not a replacement for historical status at the event time.
Work through it
- Define whether status takes effect at event time or posting time, then store non-overlapping intervals with account identity and an explicit boundary convention.
- Join using the declared timestamp and interval, validating that each transaction has exactly one applicable status or a visible unknown-status exception.
- Handle backdated corrections through versioned snapshots and compare transaction counts and amounts before and after enrichment to detect multiplication or omission.
Tradeoffs to discuss
- Maintaining interval history adds storage and correction logic but supports reproducible reporting; a current-state table is cheaper for present-tense questions.
Common pitfalls
- Avoid inclusive boundaries at both ends when adjoining intervals share a timestamp.
- Do not use arbitrary row ordering to resolve overlapping statuses; surface the conflicting source history.