Welcome to the SQL screen.
The question is on your right: Normalize Customer Addresses by Region. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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.
| Column | Type | Description |
|---|---|---|
| address_idPK | INT | Address record ID |
| customer_name | VARCHAR(100) | Customer name |
| street_address | VARCHAR(150) | Raw street address |
| city | VARCHAR(100) | City name |
| province_raw | VARCHAR(50) | Raw province or state value |
| country_raw | VARCHAR(50) | Raw country value |
| Column | Type | Description |
|---|---|---|
| province_codePK | VARCHAR(10) | Standard province or state code |
| province_name | VARCHAR(100) | Standard province or state name |
| country_code | VARCHAR(10) | Country code for the province or state |
| Column | Type | Description |
|---|---|---|
| country_code | VARCHAR(10) | Standard country code |
| country_name | VARCHAR(100) | Standard country name |
| country_aliasPK | VARCHAR(100) | Accepted raw alias for the country |