SQL Challenges · THE CHRISTMAS HEIST!
Package Chain of Custody
Difficulty: hard
Detective Quackers eyes widen. Thats not Coach Waddles—there must be multiple ducks involved in this operation! Agent Smith Duck appears from the shadows in dark sunglasses, data matrices swirling. Detective! If this is a coordinated effort, we need to follow the packages themselves. Packages move through an 8-checkpoint logistics chain: received → inspected → stored → picked → packed → loaded → in_transit → delivered. Some packages were SPLIT during processing, creating parent-child relationsh…
Your task
Starting from package_id = 186, count all tracking events for this package AND all packages split from it. Child packages have parent_package_id pointing to their parent. Use a recursive CTE: start with the original package, then recursively find all descendants. You will be working with `dbquacks_xmas.package_tracking_events` (MotherDuck) table.