SQL Challenges · THE CHRISTMAS HEIST!
The GPS Surveillance Net
Difficulty: medium
Federal agents arrive with advanced surveillance equipment. The investigation has escalated! Weve identified the target district, and now we need to track our suspects movements. We recovered Coach Waddles GPS transponder data—every location ping from his truck for the entire month of December. Thats millions of coordinates, recorded every minute. Find which district he visited most within 3km of the warehouse (latitude 45.5200, longitude -122.6800). This data will reveal his complete movement…
Your task
Find which district had the most GPS pings from Coach Waddles within 3km of the warehouse (latitude 45.5200, longitude -122.6800). GPS events have a delivery_id, deliveries have a household_id, and households have a district. You will be working with the `dbquacks_xmas.gps_tracking_events` (MotherDuck), `deliveries_log` (local), and `duck_households` (local) tables.<br />Note: You'll need to use DuckDB's <a href='https://duckdb.org/docs/stable/core_extensions/spatial/overview' target='_blank' rel='noopener noreferrer' class='text-blue-400 hover:text-blue-300 underline'>spatial extension</a> for this challenge.
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.
Database: https://dbquacks.com/data/databases/christmas_local.duckdb
This page as markdown: https://dbquacks.com/challenges/christmas/4.md