Monetary Pay Computation And Event-Time Aggregation

What's being tested

These problems test event-time interval aggregation: converting unordered delivery/work events into valid time ranges, splitting ranges across rate windows, and summing monetary pay exactly. Interviewers are probing for clean data manipulation in Python or SQL, robust edge-case handling, and readable business-rule implementation.

Patterns & templates

• Sweep line over start/end events — sort by timestamp, maintain active count/state, accumulate duration * rate; O(n log n) time.

• Interval intersection helper — overlap = max(0, min(end1,end2) - max(start1,start2)); reuse for peak windows, shifts, and deliveries.

• Normalize then compute — parse timestamps, validate start < end, dedupe IDs/events, sort chronologically before applying pay rules.

• Segment by rate windows — split one delivery across base, peak, bonus, or minimum-pay windows; never apply one multiplier to the whole interval blindly.

• Per-day aggregation — group by driver_id, DATE(ts), or local service day; watch midnight crossings and timezone assumptions.

• SQL window functions — use LAG, LEAD, ROW_NUMBER, and SUM(...) OVER (...) to pair events and detect malformed sequences.

• Money representation — compute in integer cents or Decimal; avoid binary floating-point drift in final pay totals.

Common pitfalls

Pitfall: Treating delivery duration as dropoff - pickup without checking overlap against active dash time, peak windows, or day boundaries.

Pitfall: Assuming events are ordered and unique; production-style logs often contain duplicates, missing ends, or contradictory state transitions.

Pitfall: Rounding each segment early; accumulate precise cents/decimals first, then round only at the final contractual boundary.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.