SQL Challenges · THE CHRISTMAS HEIST!
The Christmas Miracle Revealed
Difficulty: hard
Detective Quacker stands up, realization dawning. Packages kept warm... delivered to low-income districts... packages split and redistributed... This cant be a crime! Everyone gathers as Detective Quacker prepares the final revelation. Time for the ultimate query, Duckbert! This is it—the complete picture. I need to count how many deliveries meet ALL these criteria: theyre part of the secret_santa program (check the metadata) AND they went to low-income households. This number will reveal the t…
Your task
Count how many successful deliveries went to low-income households AND were part of the secret_santa program. You will be working with `deliveries_log` and `duck_households` 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 |
Database: https://dbquacks.com/data/databases/christmas_local.duckdb
This page as markdown: https://dbquacks.com/challenges/christmas/15.md