# Duck Dynasty: The Recursive Family Saga

DB Quacks SQL Tutorial · level 28 · chapter 5

Dr. Stork returns with touching news! "Families want reunion events at the festival! Grandpa Quackers (parent ID 1) has family scattered across generations but wants to reunite. We need to trace ALL his descendants through every generation for proper invitations!"

## Task

Find all descendants of Grandpa Quackers (parent ID 1) through multiple generations using the 'data/duck_family.csv' file. You will be using recursive CTEs to traverse family relationships.

## Hint

Look for a special type of CTE that can reference itself to follow relationships through multiple levels.

## Expected result

A list of all descendant duck names of Grandpa Quackers (parent ID 1) through multiple generations.

## 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_family.csv` (10 rows)

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

| column | type |
| --- | --- |
| duck_id | BIGINT |
| parent_id | BIGINT |
| relationship | 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/28

## Reference

https://duckdb.org/docs/stable/sql/query_syntax/with.html#recursive-ctes-with-using-key