Challenge
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Sightings audit: Step 1
Professor Pi is auditing the field reports and needs three queries from you.
Step 1: who really reports. Return reporter (trimmed and lowercase), reports (number of sightings) and first_seen (the earliest seen_on) — one row per real person, sorted by reporter.
-- Step 1: one row per real reporterExercise 2 — Sightings audit: Step 2
Step 2: decode the Azure City codes.
For the sightings whose ref_code starts with 'AZ', return ref_code, number (the last 4 characters converted to an integer, so '0017' becomes 17) and spot (the part of location after ' - '). Sort by number.
-- Step 2: decode the AZ codesExercise 3 — Sightings audit: Step 3
Step 3: match the messy reports against the official list.
For every sighting return reported (codemon as typed), official (the matching codemon.name, or 'UNKNOWN' when there is none) and level_gap: the absolute difference between the reported level and the official level, NULL when the reported level is missing ('' or 'unknown') or the Codémon is unknown. Match with LEFT JOIN on the lowercase, trimmed names and use CASE for the missing levels. Sort by sighting_id.
-- Step 3: messy reports vs the official list