# Hot Duck Summer

THE CLOUD EXPEDITION! · level 5 · chapter 2

Marina leans in with a competitive grin. 'Alright hotshot — which of our ducks are the sun-chasers? The ones who LEFT home for warmer waters?' She crosses her wings. 'You'll need to join four tables — the global pond registry shows up twice — to crack this. Think you can handle it?' This calls for <span class='feature-highlight'>Multi-Table Joins</span> — chaining multiple joins across local and cloud tables.

## Task

Which ducks chased warmer weather? Find Pondville ducks whose travel destination was warmer than their home pond. Return <b>name</b>, <b>home_temp</b>, <b>dest_temp</b>, and <b>destination_pond</b> — biggest temperature upgrades first, 10 rows. Tables: `pondville_residents` (local, has `home_pond`), `dbquacks_cloud_expedition.duck_travels` (cloud, links ducks to destinations), `dbquacks_cloud_expedition.global_ponds` (cloud, has `avg_temperature` for each `pond_name`).

## Hint

You'll need to join global_ponds twice — once for the home pond temperature and once for the destination. Think about how to alias each join differently

## Expected result

Up to 10 rows with columns: name, home_temp, dest_temp, and destination_pond — only ducks whose destination was warmer

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

A table in a MotherDuck share; its columns are not listed here.

### `dbquacks_cloud_expedition.global_ponds`

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/5

## Reference

https://duckdb.org/docs/stable/sql/query_syntax/from#joins