# Weather Forensics at Scale

THE CHRISTMAS HEIST! · challenge 8 · hard

> Detective Quacker receives meteorological surveillance data. "The weight exceeds what one duck could carry... but why would someone risk so much?" He opens an email. "The investigation deepens! We've obtained high-frequency weather monitoring data—reports every 5 minutes from all districts throughout December. That's a lot of weather snapshots! Now match Coach Waddles' GPS pings to the nearest prior weather report in each location. You'll need to process millions of records to find which GPS coordinates experienced the worst wind conditions. Maybe the weather will reveal why someone would make so many trips!"

## Task

Find the highest wind speed (in km/h) that Coach Waddles (duck_id = 1) drove through during any delivery. Match each GPS ping to the most recent weather report at or before that timestamp using ASOF JOIN. Weather reports are recorded per district, so you'll need to determine the district for each GPS event. You will be working with `dbquacks_xmas.gps_tracking_events` (MotherDuck), `dbquacks_xmas.weather_reports` (MotherDuck), `deliveries_log` (local), and `duck_households` (local) tables.

## Tables

### `deliveries_log` (10000 rows)

| column | type |
| --- | --- |
| delivery_id | INTEGER |
| household_id | INTEGER |
| delivered_by | INTEGER |
| delivery_time | TIMESTAMP |
| success | BOOLEAN |
| parent_delivery_id | INTEGER |
| delivery_metadata | JSON |

### `duck_households` (200 rows)

| column | type |
| --- | --- |
| household_id | INTEGER |
| address | VARCHAR |
| district | VARCHAR |
| latitude | DOUBLE |
| longitude | DOUBLE |
| household_size | INTEGER |
| income_level | VARCHAR |
| reported_missing | BOOLEAN |

### `dbquacks_xmas.gps_tracking_events`

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

### `dbquacks_xmas.weather_reports`

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/8

## Answer

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