ORIGINAL COMPANY-CONTEXT PRACTICE
Count outage impact at the subscriber grain
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.
Written for independent practice using company context; this is not a verified question from a company interview.
A REASONED APPROACH
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.