DB Quacks SQL Tutorial · Chapter 5
WITH Great Power: The CTE Symphony
Maestro duck appears with a conductors baton. Welcome to Query Symphony Orchestra! Complex analysis needs multiple movements working harmoniously. First find ducks older than 3, then show only frequent pond visitors (ducks who visited ponds more than 2 times). Use CTEs!
Your task
Create a multi-step analysis using CTEs - first find older ducks from the ducks table, then filter by visit frequency from 'data/pond_visits.csv'. You will be using Common Table Expressions to break down complex logic.
Hint
Look for a way to create temporary named result sets that you can reference in your main query.
Expected result
A table showing older duck names who are frequent pond visitors (more than 2 visits).
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/pond_visits.csv (40 rows)
| column | type |
|---|---|
visit_id | BIGINT |
duck_id | BIGINT |
pond_id | BIGINT |
visit_date | DATE |
duration_minutes | BIGINT |
Database: https://dbquacks.com/data/databases/tutorial.duckdb
This page as markdown: https://dbquacks.com/tutorial/27.md