SQL Challenges · WEEKLY QUACK CHALLENGE!
THE INSIDE JOB
Difficulty: medium
Postmaster Scoter bursts into the sorting room, a torn parcel under one wing. Parcels have been vanishing from the night sort for days, Duckbert, and last week we caught Greta Grabwell with a beakful of birthday cards. But nobody empties a mail sack that fast alone. Bruno swears hes innocent — he just cant stop punching the clock twice. Daisy sorts beside Greta every single morning, but the parcels only ever vanish after dark. And Hugo practically lives on the night sort, mostly on nights Greta…
Your task
Return the name of the duck, other than GRETA GRABWELL, who worked the most night shifts alongside her. A duck worked a shift if it has at least one `timeclock` punch for it; extra punches on the same shift still make one shift. Only shifts whose `kind` is `night` count. On a tie, return the alphabetically first name. You will be working with the `ducks`, `shifts`, and `timeclock` tables.
Tables
ducks (10 rows)
| column | type |
|---|---|
duck_id | INTEGER |
name | VARCHAR |
shifts (20 rows)
| column | type |
|---|---|
shift_id | INTEGER |
starts_at | TIMESTAMP |
kind | VARCHAR |
timeclock (61 rows)
| column | type |
|---|---|
punch_id | INTEGER |
shift_id | INTEGER |
duck_id | INTEGER |
punched_at | TIMESTAMP |
Database: https://dbquacks.com/data/databases/weekly_202643_16_inside_job.duckdb
This page as markdown: https://dbquacks.com/challenges/weekly/16.md