← ALL CHALLENGES
CHALLENGE / BEGINNER / READY TO QUERY
Where and when are sea turtles reported most often?
Practice grouping reported sea turtle encounters by year, event type and location.
01 / THE QUESTION
Question
Which years and reported locations have the most sea turtle encounters in this Canadian dataset? Do the most common event types differ by year?
02 / SOURCE TABLES
Tables to use
03 / RELATIONSHIP
ER diagram
The table relationship will be documented when a compatible dataset is available.
04 / ANALYST GUIDE
Guide
Analysis requirements
- Count source encounter records by
year,location_nameorevent_type. - Keep missing location names visible or filter them explicitly; do not invent a location from coordinates.
- Reporting is opportunistic and concentrated near Newfoundland and Labrador. More reports do not prove more turtles in the water.
- For a map, use the reported
latitudeandlongitudefields when both are present.
Suggested query
Expand suggested query
SQL / STARTER QUERY
SELECT year, event_type, COUNT(*) AS reported_encounters
FROM dfo_sea_turtle_observations
WHERE year IS NOT NULL
GROUP BY year, event_type
ORDER BY reported_encounters DESC, year
LIMIT 30;