DB Quacks SQL Tutorial · Chapter 5
Duck Dynasty: The Recursive Family Saga
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!
Your 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)
| column | type |
|---|---|
duck_id | BIGINT |
parent_id | BIGINT |
relationship | VARCHAR |
Database: https://dbquacks.com/data/databases/tutorial.duckdb
This page as markdown: https://dbquacks.com/tutorial/28.md