SQL Challenges · THE CHRISTMAS HEIST!
The Weighted Evidence
Difficulty: medium
Detective Quacker scratches his head. Thats a lot of missing time from the security system! Engineer Barnaby waddles in with load calculations and physics formulas. Detective! Ive been analyzing those security gaps. I need to test a theory—could ONE duck carry all these stolen presents in those time windows? Calculate the total weight in kilograms of all reported missing presents. If it exceeds 20kg, were looking at multiple suspects or multiple trips. Science doesnt lie!
Your task
Calculate the TOTAL weight in kilograms of ALL reported missing presents combined (not per individual report). You will be working with `missing_presents_report` and `present_inventory` 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 |
present_inventory (150 rows)
| column | type |
|---|---|
present_id | INTEGER |
item_name | VARCHAR |
category | VARCHAR |
original_quantity | INTEGER |
current_quantity | INTEGER |
weight_kg | DECIMAL(5,2) |
warehouse_location | VARCHAR |
last_audit_date | DATE |
Database: https://dbquacks.com/data/databases/christmas_local.duckdb
This page as markdown: https://dbquacks.com/challenges/christmas/7.md