DB Quacks SQL Tutorial · Chapter 5
Back to the Future: LAG and LEAD
Time-traveling scientist Dr. Temporal appears with a glowing device. Great Scott! I need temporal analysis - compare each pond visit with previous and next visit dates to understand behavioral trends!
Your task
Show each pond visit with the previous and next visit dates for comparison using the 'data/pond_visits.csv' file. You will be using window functions to access adjacent rows.
Hint
Look for window functions that can access data from the previous or next row in your result set.
Expected result
A table showing each visit with duck names and previous/next visit dates.
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/25.md