# The Hundred-Million Row March

THE CLOUD EXPEDITION! · level 18 · chapter 7

A duck in a lab coat skids to a halt — Dr. Velocity! 'Forget toy datasets, Duckbert. We're about to crunch 100 MILLION migration records!' She slams a comically oversized calculator on the table. 'MotherDuck eats big data for breakfast.' Time for <span class='feature-highlight'>Cloud Scale Analytics</span> — aggregating massive datasets in the cloud.

## 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.

## Data

The tables live in a DuckDB database: https://dbquacks.com/data/databases/motherduck_tutorial.duckdb

To query it outside the browser, attach it read-only:

```sql
ATTACH 'https://dbquacks.com/data/databases/motherduck_tutorial.duckdb' AS db (READ_ONLY);
USE db;
```

This level also queries MotherDuck shares, which need a MotherDuck token. On the page, the editor asks for one.

Play it in the browser: https://dbquacks.com/motherduck/18

## Reference

https://duckdb.org/docs/stable/sql/functions/aggregates