ORIGINAL COMPANY-CONTEXT PRACTICE
Count shipped orders without multiplying package rows
Original practice scenario: A hypothetical fulfillment dataset has orders, order lines, packages and package events. Explain how to calculate distinct shipped orders per day when orders span multiple packages.
Written for independent practice using company context; this is not a verified question from a company interview.
A REASONED APPROACH
Model answer.
Define what shipped means at order grain before joining event tables. One order may have several lines, packages and scans, each of which can multiply the apparent count.
Work through it
- Choose the qualifying event and time rule, including whether the order counts after its first package or only after all required packages ship.
- Deduplicate package events by event identity, derive the relevant shipment timestamp per package, then aggregate to one row per order.
- Join that order-level result to the reporting calendar and group by the declared time zone, preserving unshipped orders for separate completeness controls.
Tradeoffs to discuss
- First-package and fully-shipped metrics answer different questions; either can be valid if its population and timestamp are explicit.
Common pitfalls
- COUNT DISTINCT can hide multiplication while leaving sums and timestamps wrong.
- Handle split shipments, reversed scans and missing package mappings instead of treating every event as a completed order.