# Spot the Difference

THE CLOUD EXPEDITION! · level 16 · chapter 6

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 <span class='feature-highlight'>Version Comparison</span> — diffing two snapshots of the same table to find what changed.

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

## 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/16

## Reference

https://ducklake.select/docs/stable/duckdb/usage/time_travel