THE CLOUD EXPEDITION! · Chapter 7
The Hundred-Million Row March
A duck in a lab coat skids to a halt — Dr. Velocity! Forget toy datasets, Duckbert. Were about to crunch 100 MILLION migration records! She slams a comically oversized calculator on the table. MotherDuck eats big data for breakfast. Time for Cloud Scale Analytics — aggregating massive datasets in the cloud.
Your task
Break down migration patterns by year and season. For each period, show <b>migration_year</b>, <b>migration_season</b>, flight count (<b>total_flights</b>), average flock size rounded to 2 decimals (<b>avg_flock_size</b>), and average distance rounded to 2 decimals (<b>avg_distance</b>). Order chronologically by year, then season. Table: `dbquacks_cloud_expedition.global_migrations` (MotherDuck). Relevant columns: `flock_size`, `distance_km`.
Hint
Break the data down by year and season to spot trends over time
Expected result
Rows per year/season with columns: migration_year, migration_season, total_flights, avg_flock_size, avg_distance
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.global_migrations
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/18.md