# Endgame - Data Assembly

DB Quacks SQL Tutorial · level 38 · chapter 7

Festival day arrives! All your Pondville friends gather at the floating festival stage. "Duckbert!" calls Professor Shellbert. "Ducks are arriving from everywhere - families reuniting, interdimensional visitors, cloud communities! We need perfect timing coordination." The Doctor Duck materializes with festival schedules. "Match everyone's current location with migration history for perfect arrival timing!"

## Task

Use ASOF JOIN to create perfect timing by matching current duck locations from 'data/duck_locations.csv' with their migration history from 'data/migration_history.parquet'. You will be finding the most recent migration record for each duck.

## Hint

Look for a special type of join that can match records based on time relationships, finding the most recent record that occurred before or at a given time.

## Expected result

A table showing current duck locations matched with their most recent migration records.

## Tables

### `duck_diaries` (10 rows)

| column | type |
| --- | --- |
| diary_id | BIGINT |
| duck_id | BIGINT |
| entry_date | DATE |
| diary_text | VARCHAR |
| mood | VARCHAR |

### `duck_surveys` (5000 rows)

| column | type |
| --- | --- |
| survey_id | BIGINT |
| duck_id | BIGINT |
| age | BIGINT |
| favorite_food | VARCHAR |
| favorite_pond | BIGINT |
| swimming_rating | BIGINT |
| migration_preference | VARCHAR |
| social_behavior | VARCHAR |
| health_status | VARCHAR |
| activity_level | VARCHAR |
| survey_date | DATE |
| survey_location | BIGINT |

### `ducks` (15 rows)

| column | type |
| --- | --- |
| id | BIGINT |
| name | VARCHAR |
| age | BIGINT |
| species | VARCHAR |
| birth_date | DATE |
| arrival_date | DATE |

### `ponds` (3 rows)

| column | type |
| --- | --- |
| pond_id | BIGINT |
| pond_name | VARCHAR |
| location_lat | DOUBLE |
| location_lng | DOUBLE |
| capacity | BIGINT |
| features | VARCHAR[] |
| amenities | VARCHAR[] |

## Files

### `data/duck_locations.csv` (15 rows)

Download: https://dbquacks.com/data/duck_locations.csv

| column | type |
| --- | --- |
| duck_id | BIGINT |
| current_date | DATE |
| duck_latitude | DOUBLE |
| duck_longitude | DOUBLE |
| last_seen | TIMESTAMP |

### `data/migration_history.parquet` (20 rows)

Download: https://dbquacks.com/data/migration_history.parquet

| column | type |
| --- | --- |
| duck_id | BIGINT |
| migration_date | DATE |
| destination | VARCHAR |
| distance_km | DOUBLE |
| season | VARCHAR |

## Data

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

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

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

Play it in the browser: https://dbquacks.com/tutorial/38

## Reference

https://duckdb.org/docs/stable/guides/sql_features/asof_join