# Package Chain of Custody

THE CHRISTMAS HEIST! · challenge 11 · hard

> Detective Quacker's eyes widen. "That's 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 relationships. We have a lot of package tracking events to analyze. Starting from package_id = 186, trace its complete family tree through all checkpoint events—the original package plus all child packages created when it was split. How many total package events are connected to this chain? This might reveal how packages were redistributed!"

## 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.

## Tables

### `dbquacks_xmas.package_tracking_events`

A table in a MotherDuck share; its columns are not listed here.

## Data

The tables live in a DuckDB database: https://dbquacks.com/data/databases/christmas_local.duckdb

To query it outside the browser, attach it read-only:

```sql
ATTACH 'https://dbquacks.com/data/databases/christmas_local.duckdb' AS db (READ_ONLY);
USE db;
```

This level also queries MotherDuck shares, which need a MotherDuck token. On the page, the editor asks for one.

Play it in the browser: https://dbquacks.com/challenges/christmas/11

## Answer

The expected answer is not published. Submit on the page to have it checked.