THE CLOUD EXPEDITION! · Chapter 6
Past Meets Present Meets Shared
Chronos and Marina lock eyes. My time travel... he begins. ...plus my shared ecosystem data... she finishes. ONE QUERY. They turn to you in unison. This is the Avengers moment — two paradigms, combined for the first time. Combine Time Travel + Shared Data — joining historical snapshots with live shared databases.
Your task
Correlate storm damage with ecosystem readings. First compute the population change per district (version 1 vs current), then match each district to its ecosystem readings. Each district has several readings, so average them: return <b>district</b>, <b>pop_change</b>, <b>avg_temperature</b>, and <b>avg_water_quality</b> — biggest absolute population changes first. Tables: <code>dbquacks_living_lake.pond_census</code> (Ducklake, version 1 = pre-storm), <code>dbquacks_marina_lake.ecosystem_readings</code> (shared, has <code>pond_name</code>, <code>temperature</code>, <code>water_quality</code> — district names match pond_name).<br><br><b>Re-attach if needed:</b><ul><li><code>dbquacks_marina_lake</code> → <code>md:_share/marina_lake_db/3be07852-dfce-4b29-b415-99c9f7fdcc6a</code></li></ul>
Hint
Combine time travel with shared data — district names match pond_name; average the readings per district with GROUP BY
Expected result
Rows with columns: district, pop_change, avg_temperature, and avg_water_quality — one row per district, showing how storm population shifts correlate with ecosystem readings
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_living_lake.pond_census
A table in a MotherDuck share; its columns are not listed here.
dbquacks_marina_lake.ecosystem_readings
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/17.md