# THE FROG INVASION

WEEKLY QUACK CHALLENGE! · challenge 5 · hard · 5 guesses

> Lieutenant Teal bursts into the command room dripping wet. "Something is happening out there and I don't like it one bit. The frogs — they're multiplying. Every week the survey teams go out and count what's in each pond and I have been watching the numbers and Duckbert I am scared. One by one these ponds are going green. The frogs are taking over. I need to know which pond fell first. Not the pond with the most frogs — any fool can count a heap of frogs. I need the pond where the balance tipped earliest. Find it before it's too late and we're all swimming in ribbits!"

## Task

Each surveyed species is a duck or a frog, classified by `read_csv(data/files/202631_05_frog_invasion/species_info.csv, header=true)`. For each pond, follow the running totals of ducks and frogs over time and find the first date its cumulative frog count overtakes its cumulative duck count. Return the name of the pond that crosses over on the earliest date; on a tie, the alphabetically first. You will be working with the `surveys` and `ponds` tables and the species file.

## Tables

### `surveys` (22 rows)

| column | type |
| --- | --- |
| survey_id | INTEGER |
| pond_id | INTEGER |
| date | DATE |
| species | VARCHAR |
| count | INTEGER |

### `ponds` (3 rows)

| column | type |
| --- | --- |
| pond_id | INTEGER |
| name | VARCHAR |

## Files

### `data/files/202631_05_frog_invasion/species_info.csv` (6 rows)

Download: https://dbquacks.com/data/files/202631_05_frog_invasion/species_info.csv

| column | type |
| --- | --- |
| species | VARCHAR |
| kind | VARCHAR |

## Data

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

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

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

Play it in the browser: https://dbquacks.com/challenges/weekly/5

## Answer

The expected answer is not published. Submit on the page to have it checked.