SQL Challenges · WEEKLY QUACK CHALLENGE!
THE LANTERN TRAIL
Difficulty: hard
Lamplighter Harlequin sets a soot-smudged stamp book on the desk. "The Golden Lantern goes to the first duck who collects a stamp at every lantern on the trail card, Duckbert. Simple, I said. Then Quinn waved a book with eleven stamps in it, Bramble counted the cocoa hut, and Dot got Heron Bridge to stamp her book twice. Every one of them thinks they won. Tell me who truly finished first before I hand this thing to the wrong duck!"
Your task
Return the name of the duck who finished the lantern trail first. The trail card is every station with `on_trail_card` set to true; stamps from any other station do not count. A duck finishes at the moment they first hold a stamp from every station on the card, visited in any order, so a repeat stamp at a station counts once and never moves that moment. Ducks missing a card station have not finished. The earliest finishing moment wins, however late a duck started. On a tie, return the alphabetically first duck name. You will be working with the `stations` and `stamps` tables.
Tables
stations (7 rows)
| column | type |
|---|---|
station_id | INTEGER |
name | VARCHAR |
on_trail_card | BOOLEAN |
stamps (51 rows)
| column | type |
|---|---|
stamp_id | INTEGER |
duck_name | VARCHAR |
station_id | INTEGER |
stamped_at | TIMESTAMP |
Database: https://dbquacks.com/data/databases/weekly_202645_18_lantern_trail.duckdb
This page as markdown: https://dbquacks.com/challenges/weekly/18.md
More: practice SQL exercises online · SQL interview questions