SQL Challenges · THE CHRISTMAS HEIST!
The Pattern Emerges
Difficulty: easy
Detective Quacker spreads out a district map on his desk with pushpins marking each theft. Weve narrowed down our suspect pool, Duckbert! Now lets see which district suffered the most thefts. Every crime has a pattern! Group our missing present reports by district and find where the criminal struck hardest. Geographic clustering always reveals something important about the perpetrators territory!
Your task
Find the district with the most theft reports. Each report has a household_id that links to a household, and each household belongs to a district. You will be working with the `missing_presents_report` and `duck_households` tables.
Tables
missing_presents_report (93 rows)
| column | type |
|---|---|
report_id | INTEGER |
household_id | INTEGER |
present_id | INTEGER |
reported_date | TIMESTAMP |
quantity_missing | INTEGER |
last_seen_location | VARCHAR |
reporter_name | VARCHAR |
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 |
Database: https://dbquacks.com/data/databases/christmas_local.duckdb
This page as markdown: https://dbquacks.com/challenges/christmas/3.md