THE CLOUD EXPEDITION! · Chapter 2
Hot Duck Summer
Marina leans in with a competitive grin. Alright hotshot — which of our ducks are the sun-chasers? The ones who LEFT home for warmer waters? She crosses her wings. Youll need to join four tables — the global pond registry shows up twice — to crack this. Think you can handle it? This calls for Multi-Table Joins — chaining multiple joins across local and cloud tables.
Your task
Which ducks chased warmer weather? Find Pondville ducks whose travel destination was warmer than their home pond. Return <b>name</b>, <b>home_temp</b>, <b>dest_temp</b>, and <b>destination_pond</b> — biggest temperature upgrades first, 10 rows. Tables: `pondville_residents` (local, has `home_pond`), `dbquacks_cloud_expedition.duck_travels` (cloud, links ducks to destinations), `dbquacks_cloud_expedition.global_ponds` (cloud, has `avg_temperature` for each `pond_name`).
Hint
You'll need to join global_ponds twice — once for the home pond temperature and once for the destination. Think about how to alias each join differently
Expected result
Up to 10 rows with columns: name, home_temp, dest_temp, and destination_pond — only ducks whose destination was warmer
Tables
lake_visits (500 rows)
| column | type |
|---|---|
visit_id | BIGINT |
duck_id | BIGINT |
destination_pond | VARCHAR |
visit_date | DATE |
purpose | VARCHAR |
pondville_events (50 rows)
| column | type |
|---|---|
event_id | BIGINT |
event_name | VARCHAR |
event_date | DATE |
organizer_id | BIGINT |
location | VARCHAR |
attendee_count | BIGINT |
pondville_residents (100 rows)
| column | type |
|---|---|
duck_id | BIGINT |
name | VARCHAR |
species | VARCHAR |
home_pond | VARCHAR |
age | BIGINT |
occupation | VARCHAR |
joined_date | DATE |
dbquacks_cloud_expedition.duck_travels
A table in a MotherDuck share; its columns are not listed here.
dbquacks_cloud_expedition.global_ponds
A table in a MotherDuck share; its columns are not listed here.
Database: https://dbquacks.com/data/databases/motherduck_tutorial.duckdb
This page as markdown: https://dbquacks.com/motherduck/5.md