ORIGINAL COMPANY-CONTEXT PRACTICE
Compare distributor shipments without double counting
Original practice scenario: A synthetic beverage dataset has shipment lines, promotion records and distributor territories. Explain a safe monthly sales query when each shipment has several promotions.
Written for independent practice using company context; this is not a verified question from a company interview.
A REASONED APPROACH
Model answer.
Declare the result grain as distributor and reporting month, and aggregate each independent many-side relation before combining it. Otherwise promotions can multiply shipment revenue.
Work through it
- Deduplicate shipment lines by their stable business key and accepted revision, then compute quantity and revenue in a documented currency and unit.
- Summarize promotion exposure separately at a compatible grain; resolve territory history using the shipment date and non-overlapping effective intervals.
- Join the summaries and reconcile totals to the shipment-only baseline, retaining distributors with no promotion and explicitly handling missing territory.
Tradeoffs to discuss
- Pre-aggregation limits row multiplication but can remove detail needed for attribution; maintain separate detailed analysis when required.
Common pitfalls
- Do not repair fanout with an unexplained DISTINCT.
- Use documented return and cancellation rules instead of silently treating every shipment as completed sales.