THE CLOUD EXPEDITION! · Chapter 3
Roll Call Across Worlds
Captain Cloudsworth rises to full height, wings spread wide. THREE ecosystems. THREE public research datasets — open to any duck on MotherDuck! He looks down at you with a conspiratorial grin. Attach them all and find out which species have conquered multiple worlds. Time for Multi-Share Analytics — attaching and querying across multiple public shared databases.
Your task
Attach the remaining two shared databases, then combine species data from all three to find which species span multiple ecosystems. Return <b>species_name</b>, <b>num_ecosystems</b>, and <b>total_population</b> — most widespread first, then by total population. Tables: <code>dbquacks_marina_lake.species</code> (attached in level 7), <code>dbquacks_arctic_pond.species</code>, <code>dbquacks_tropical_lagoon.species</code>. Each has <code>species_name</code> and <code>population_count</code> columns. Run the ATTACH commands before your SELECT — all statements together.<br><br><b>Share URLs:</b><ul><li><code>dbquacks_arctic_pond</code> → <code>md:_share/arctic_pond_db/35caa010-56d6-465b-80f2-5d67270ec0ed</code></li><li><code>dbquacks_tropical_lagoon</code> → <code>md:_share/tropical_lagoon_db/7e2b85ab-5af9-496a-870c-c7e0e20e1c65</code></li></ul><br><b>About MotherDuck shares:</b> Shares have three access levels: <b>UNRESTRICTED</b> (public — anyone can attach), <b>ORGANIZATION</b> (your team only), and <b>RESTRICTED</b> (explicit grants needed). These tutorial datasets use UNRESTRICTED access.
Hint
ATTACH the two new shared databases the same way, then use UNION ALL to stack all three species tables together
Expected result
Rows with columns: species_name, num_ecosystems, and total_population — ordered by num_ecosystems descending
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_marina_lake.species
A table in a MotherDuck share; its columns are not listed here.
dbquacks_arctic_pond.species
A table in a MotherDuck share; its columns are not listed here.
dbquacks_tropical_lagoon.species
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/8.md