# The GPS Surveillance Net

THE CHRISTMAS HEIST! · challenge 4 · medium

> Federal agents arrive with advanced surveillance equipment. "The investigation has escalated! We've 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. That's 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 patterns!"

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

## 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/4

## Answer

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