Original practice scenario inspired by AT&T Inc.'s public business context; this is not a reported interview question or a description of its internal systems. A connectivity service receives usage batches from intermittently connected network collectors. A collector retries the same batch after a timeout, and corrections can arrive days later. Design ingestion that supports auditable billing without charging twice.
Read model answer +
Treat transport retries and business corrections as separate events. A retry should reuse a stable collector batch identifier; a correction needs its own versioned relationship to the original usage.
Work through it
Persist the batch identifier, collector identity, checksum, and receipt state before acknowledging. Enforce uniqueness atomically so concurrent retries cannot both create billable rows.
Write normalized usage and an outbox record in the same transaction. Downstream billing consumes the durable event with its own idempotency key.
Represent corrections as explicit adjustments with provenance, then reconcile collector totals against accepted usage and billed adjustments.
Tradeoffs to discuss
Retaining immutable adjustments costs storage but makes replay and customer disputes explainable. Deduplication retention must exceed the permitted retry window.
Common pitfalls
A random ID generated on every retry defeats deduplication.
A successful network acknowledgement is not proof that billing completed.
Original practice scenario inspired by AT&T Inc.'s public business context; this is not a reported interview question or a description of its internal systems. A fictional outage report has incidents, incident-to-cell mappings, and subscriber-to-cell snapshots. Subscribers may use several affected cells. Explain a query for distinct affected subscribers per incident without inflating counts.
Read model answer +
Choose the incident-subscriber pair as the intermediate grain. Joining cell mappings directly produces multiple rows for a subscriber who appears on several affected cells.
Work through it
Specify which subscriber snapshot applies at incident start and whether the metric includes active subscribers only. Keep that temporal rule consistent across incidents.
Join incident cells to the selected subscriber snapshot, project incident_id and subscriber_id, and deduplicate that pair before counting.
Left join the resulting counts back to incidents so incidents with no matched subscribers remain visible with zero. Validate against a small overlapping-cell example.
Tradeoffs to discuss
A deduplicated intermediate can be large. Partition pruning and indexes help, but a precomputed cell count cannot simply be summed when subscriber overlap exists.
Common pitfalls
COUNT(*) measures join rows.
COUNT(DISTINCT subscriber_id) without grouping by incident silently changes the question to overall reach.
