Weather Forensics at Scale

Difficulty: 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! Weve obtained high-frequency weather monitoring data—reports every 5 minutes from all districts throughout December. Thats a lot of weather snapshots! Now match Coach Waddles GPS pings to the nearest prior weather report in each location. Youll need to process millions of records to find which GPS coordinat…

Your 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