ORIGINAL COMPANY-CONTEXT PRACTICE
Compute vehicle telemetry windows without future leakage
Original practice scenario: A hypothetical fleet analysis estimates energy use per trip from irregular telemetry. Trips cross midnight and some readings arrive after the analysis run. Define the query boundaries.
Written for independent practice using company context; this is not a verified question from a company interview.
A REASONED APPROACH
Model answer.
Start from explicit trip identities and an event-time interval rather than calendar-day grouping. Preserve when each reading became available if the query is intended to reproduce a past decision.
Work through it
- Partition readings by vehicle and sensor generation, deduplicate their identities, and order by event time with a deterministic tie-break rule.
- Calculate valid consecutive differences within trip boundaries, handling resets and long gaps separately instead of blindly summing every counter change.
- Use the declared reporting zone for daily allocation and an arrival-time cutoff for reproducible historical runs; retain missing-coverage indicators beside totals.
Tradeoffs to discuss
- Interpolation improves coverage for short gaps but assumes behavior between observations; reporting observed-only totals is conservative while potentially undercounting.
Common pitfalls
- Do not connect the final reading of one trip to the first reading of another.
- Late backfills must not silently change an already reviewed snapshot without a new result version.