You are given raw customer addresses exported from a TELUS Digital support workflow. The data contains inconsistent province and country values such as mixed casing, abbreviations, punctuation, and missing region mappings. Write a PostgreSQL query that returns one row per address with a normalized province code and normalized country name, then classifies each record as normalized, needs_review, or invalid.
Use the lookup tables to match cleaned province and country values. If a province cannot be matched but the country is Canada or the United States, mark the row as needs_review. If the country itself cannot be matched, mark the row as invalid. Otherwise mark the row as normalized.
Output details
- Return columns in this order:
address_id, customer_name, normalized_province_code, normalized_country_name, address_status.
- Sort results by
address_id ascending only, without a secondary tie-breaker.
- Normalize values by trimming, removing nonletters, uppercasing, and treating empty results as
NULL.
- Match countries using cleaned country aliases and provinces using cleaned province names or codes within the matched country.
- Preserve duplicate matches, return all resulting rows, and apply no deduplication or row limit.
- Leave unmatched
normalized_province_code and normalized_country_name values as SQL NULL.
