SQL Challenges · THE CHRISTMAS HEIST!
The Elite Performers
Difficulty: medium
Detective Quacker paces the room. Driving through blizzard conditions... somethings not adding up. This doesnt feel like a criminal! General Gander arrives with strategic analysis charts and tactical maps covering all districts. Detective! Ive been reviewing the case files. Strategic profiling requires comparative analysis! For EACH district separately, I need to identify the volunteer with the most deliveries. Some volunteers excel in multiple districts, others in just one. Heres the question:…
Your task
Find all volunteers who rank #1 (most deliveries) in at least one district, then count ALL their deliveries across the entire dataset. Use the `delivered_by` column in deliveries_log to identify volunteers. You will be working with `deliveries_log` and `duck_households` tables. Note: It's possible for the same volunteer (like duck_id 1) to rank #1 in multiple districts - this is expected and correct.
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 |
Database: https://dbquacks.com/data/databases/christmas_local.duckdb
This page as markdown: https://dbquacks.com/challenges/christmas/9.md