THE CLOUD EXPEDITION! · Chapter 6
Spot the Difference
Marina bursts in, eyes blazing. The storm changed EVERYTHING — but which districts got hit hardest? Chronos raises a feathered brow. Compare then and now. The data will confess what the storm tried to hide. This is Version Comparison — diffing two snapshots of the same table to find what changed.
Your task
Compare the pre-storm census (version 1) with the current data. For each district, show <b>district</b>, <b>pop_before_storm</b>, <b>pop_after_storm</b>, and <b>population_change</b> (the difference). Biggest absolute changes first. Table: `dbquacks_living_lake.pond_census` (Ducklake — version 1 is the pre-storm snapshot, current version is post-storm).
Hint
Use a CTE to capture the version 1 snapshot, then JOIN it with the current table on district
Expected result
Rows with columns: district, pop_before_storm, pop_after_storm, and population_change — ranked by biggest absolute change
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.
Database: https://dbquacks.com/data/databases/motherduck_tutorial.duckdb
This page as markdown: https://dbquacks.com/motherduck/16.md