← 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_name or event_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 latitude and longitude fields 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;