Task
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.
Schema
| Table | Column | Type | Description |
|---|
| customer_addresses | address_id | INT | Address record ID |
| customer_addresses | customer_name | VARCHAR(100) | Customer name |
| customer_addresses | street_address | VARCHAR(150) | Raw street address |
| customer_addresses | city | VARCHAR(100) | City name |
| customer_addresses | province_raw | VARCHAR(50) | Raw province/state value |
| customer_addresses | country_raw | VARCHAR(50) | Raw country value |
| province_reference | province_code | VARCHAR(10) | Standard province/state code |
| province_reference | province_name | VARCHAR(100) | Standard province/state name |
| province_reference | country_code | VARCHAR(10) | Country code for province/state |
| country_reference | country_code | VARCHAR(10) | Standard country code |
| country_reference | country_name | VARCHAR(100) | Standard country name |
| country_reference | country_alias | VARCHAR(100) | Acceptable raw country alias |
Sample data
| address_id | customer_name | city | province_raw | country_raw |
|---|
| 1 | Ava Chen | Toronto | on | canada |
| 2 | Liam Patel | Vancouver | British Columbia | CA |
| 4 | Noah Smith | Seattle | wa. | usa |
| 7 | Sophia Brown | Montreal | | Canada |
| 10 | Mia Wilson | Calgary | Alberta | CAN |
Expected output
| address_id | customer_name | normalized_province_code | normalized_country_name | address_status |
|---|
| 1 | Ava Chen | ON | Canada | normalized |
| 7 | Sophia Brown | NULL | Canada | needs_review |
| 8 | James Lee | NULL | NULL | invalid |