SQL Challenges · THE CHRISTMAS HEIST!
Multi-Checkpoint Access Patterns
Difficulty: hard
Detective Quacker reviews the evidence board. So many deliveries by elite performers... but we still need to understand who had access to pull this off! Maestro Maurice conducts with his baton over a swirling data symphony. Detective! Ive been analyzing those security checkpoint events Dr. Temporal provided. The advanced security system has FOUR checkpoint types: gate, door, badge_scan, and camera_detection. Thats a lot of security events to analyze! We need to find volunteers who accessed ALL…
Your task
Find the name of the volunteer who accessed all 4 checkpoint types (gate, door, badge_scan, camera_detection) AND has the most total checkpoint events. You will be working with `dbquacks_xmas.security_checkpoint_events` (MotherDuck) and `volunteer_ducks` (local) tables.
Tables
volunteer_ducks (50 rows)
| column | type |
|---|---|
duck_id | INTEGER |
name | VARCHAR |
specialty | VARCHAR |
shift_start | TIME |
shift_end | TIME |
experience_level | INTEGER |
background_check_date | DATE |
home_district | VARCHAR |
dbquacks_xmas.security_checkpoint_events
A table in a MotherDuck share; its columns are not listed here.
Database: https://dbquacks.com/data/databases/christmas_local.duckdb
This page as markdown: https://dbquacks.com/challenges/christmas/10.md